<?php

namespace app\common\controller;

use app\admin\model\BoxAd;
use app\admin\model\BoxAdLog;
use app\admin\model\BoxAdMedia;
use app\admin\model\BoxBannerLog;
use app\admin\model\BoxDataOverview;
use app\admin\model\BoxDeviceRemain;
use app\admin\model\BoxFacilityRemainLog;
use app\admin\model\BoxGame;
use app\admin\model\BoxGameDataAllLog;
use app\admin\model\BoxGameLog;
use app\admin\model\BoxGameMemberLostLog;
use app\admin\model\BoxGameMemberLtvLog;
use app\admin\model\BoxGameMemberPayRemainLog;
use app\admin\model\BoxGameMemberRemainLog;
use app\admin\model\BoxGamePayFirstLog;
use app\admin\model\BoxGameRemainLog;
use app\admin\model\BoxMemberInfo;
use app\admin\model\BoxMemberltvLog;
use app\admin\model\BoxMemberpayLtvLog;
use app\admin\model\BoxMemberpayRateLog;
use app\admin\model\BoxMemberRemain;
use app\admin\model\BoxMemberRemainLog;
use app\admin\model\BoxMpayRemainLog;
use app\admin\model\BoxStatisticsAdGameTrackLog;
use app\admin\model\BoxStatisticsAdLtvLog;
use app\admin\model\BoxStatisticsAdOverviewLog;
use app\admin\model\BoxStatisticsAdRemainLog;
use app\admin\model\BoxStatisticsAdTrackLog;
use app\admin\model\BoxStatisticsMemberOverviewAllLog;
use app\admin\model\BoxStatisticsMemberOverviewLog;
use app\admin\model\BoxYunOrder;
use app\admin\model\BoxYunUserMachine;
use app\admin\model\OrderSub;
use app\admin\model\Package;
use app\admin\model\PromotePackageLtv;
use app\admin\model\PromotePackagePayLtv;
use app\admin\model\PromotePackagePayRate;
use app\admin\model\PromotePackagePayRemain;
use app\admin\model\PromotePackageRemain;
use app\admin\model\PromotePackageStats;
use app\admin\model\recharge\BoxRechargeGame;
use app\admin\model\recharge\BoxRechargeOrder;
use app\admin\model\RegisterLog;
use app\admin\model\Settlement;
use app\admin\model\SettlementPre;
use app\admin\model\SettlementStatement;
use app\admin\model\SettlementViolation;
use app\admin\model\UserPromote;
use app\admin\model\yun\YunPhoneCdkCode;
use app\admin\model\yun\BoxYunFreeGetLog;
use app\admin\model\yun\BoxYunGoodLog;
use app\admin\service\BoxReportMemberOverviewService;
use app\admin\service\BoxStatisticsGameOverviewService;
use app\advertising\model\BoxAdStatisticalDataLog;
use app\qsapi\model\vip\BoxVipOrder;
use Exception;
use think\Controller;
use think\Db;


/**
 * 公共资源-导出Excel
 */
class Export extends Controller
{

    public function exportExcel($expTitle, $expCellName, $expTableData)
    {
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);
        $fileName = $expTitle;
        $cellNum  = count($expCellName);
        $dataNum  = count($expTableData);
        require_once VENDOR_PATH . 'phpoffice/phpexcel/Classes/PHPExcel.php';
        $objPHPExcel = new \PHPExcel();
        $cellName    = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        for ($i = 0; $i < $cellNum; $i++) {
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '2', $expCellName[$i][1]);
        }
        for ($i = 0; $i < $dataNum; $i++) {
            for ($j = 0; $j < $cellNum; $j++) {
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 3), $expTableData[$i][$expCellName[$j][0]]);
            }
        }
        ob_end_clean();//清除缓冲区,避免乱码
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    public function expUser($id, $map,$promote_level = 1)
    {
        switch ($id) {
            case 1:
                $xlsName  = "订单数据";
                $xlsCell  = array(
                    array('pay_order_number', '支付订单号'),
                    array('member_account', '玩家账号'),
                    array('server_id', '区服id'),
                    array('server_name', '区服名称'),
                    array('game_role_id', '角色id'),
                    array('game_role_name', '角色名'),
                    array('package_id', '游戏包id'),
                    array('package_name', '游戏包名称'),
                    array('app_id', '项目ID'),
                    array('app_name', '项目名称'),
                    array('promote_account', '所属推广员'),
                    array('promote_2_account', '所属二级推广员'),
                    array('promote_3_account', '所属三级推广员'),
                    //array('promote_1_account', '所属一级推广员'),
                    array('props_name', '道具名称'),
                    array('pay_amount', '支付金额'),
                    array('pay_way', '支付方式'),
                    array('order_ip', '支付IP'),
                    array('transaction_id', '交易流水'),
                    array('is_sandbox', '是否沙盒'),
                    array('collect_account', '收款账号'),
                    array('pay_time', '支付时间'),
                );
                if($promote_level > 1){
                    $xlsCell  = array(
                        array('pay_order_number', '支付订单号'),
                        array('member_account', '玩家账号'),
                        array('server_id', '区服id'),
                        array('server_name', '区服名称'),
                        array('game_role_id', '角色id'),
                        array('game_role_name', '角色名'),
                        array('package_id', '游戏包id'),
                        array('package_name', '游戏包名称'),
                        array('app_id', '项目ID'),
                        array('app_name', '项目名称'),
                        array('promote_account', '所属推广员'),
                        array('promote_2_account', '所属二级推广员'),
                        array('promote_3_account', '所属三级推广员'),
                        //array('promote_1_account', '所属一级推广员'),
                        array('props_name', '道具名称'),
                        array('pay_amount', '支付金额'),
                        array('pay_way', '支付方式'),
                        array('order_ip', '支付IP'),
                        array('transaction_id', '交易流水'),
                        array('is_sandbox', '是否沙盒'),
                        array('pay_time', '支付时间'),
                    );
                }
                $xlsData  = Db::name('order')
                    ->where($map)
                    ->select();
                $payWay   = Db::name('pay_way')->select();
                $gameInfo = Db::name('game')->field('id,app_id')->select();
                $arr      = [];
                foreach ($payWay as $k => $v) {
                    $arr[$v['id']] = $v['name'];
                }
                $gameData = [];
                foreach ($gameInfo as $k => $v) {
                    $gameData[$v['id']]['app_id']   = $v['app_id'];
                    $gameData[$v['id']]['app_name'] = Db::name('app')->where('id', $v['app_id'])->value('app_name');
                }
                foreach ($xlsData as $k => &$v) {
                    $v['pay_time']       = date("Y-m-d H:i:s", $v['pay_time']);
                    $v['member_account'] = substr_replace($v['member_account'], '*****', 2, -2);
                    $v['pay_way']        = isset($arr[$v['pay_way']]) ? $arr[$v['pay_way']] : '未知';
                    $v['is_sandbox']     = $v['is_sandbox'] == 0 ? '否' : '是';
                    $v['app_id']         = isset($gameData[$v['game_id']]) ? $gameData[$v['game_id']]['app_id'] : '未知';
                    $v['app_name']       = isset($gameData[$v['game_id']]) ? $gameData[$v['game_id']]['app_name'] : '未知';
                    //$v['promote_1_account'] = Db::name('user_promote')->where('id',getPromote1($v['promote_id']))->value('realname');
                    $v['promote_2_account'] = Db::name('user_promote')->where('id', getPromote2($v['promote_id']))->value('realname');
                    $v['promote_3_account'] = getPromote3($v['promote_id'])['realname'];
                }
                break;
            case 2:
                $xlsName  = "分账订单数据";
                $xlsCell  = array(
                    array('pay_order_number', '支付订单号'),
                    array('sub_order_no', '分账单号'),
                    array('status', '分账状态'),
                    array('sub_mch_name', '服务商全称'),
                    array('mch_id', '服务商商户号'),
                    array('sub_mch_id', '子商户号'),
                    array('pay_amount', '支付金额'),
                    array('scale', '分账比例 %'),
                    array('sub_amount', '分账金额'),
                    array('add_time', '支付时间'),
                    array('sub_time', '请求分账时间'),
                    array('transaction_id', '微信支付订单号'),
                );
                $orderSub = new OrderSub();
                $xlsData  = $orderSub
                    ->where($map)
                    ->select();
                foreach ($xlsData as $k => &$v) {
                    if ($v['status'] == 1) {
                        $v['status'] = '未请求';
                    }
                    if ($v['status'] == 2) {
                        $v['status'] = '分账处理中';
                    }
                    if ($v['status'] == 3) {
                        $v['status'] = '分账完成';
                    }
                }
                break;
            case 3:
                $xlsName    = "结算单";
                $xlsCell    = array(
                    array('settlement_number', '结算单号'),
                    array('record_date', '结算周期'),
                    array('promote_1_account', '一级推广员'),
                    array('promote_account', '结算推广员'),
                    array('total_money', '总充值'),
                    array('total_number', '总注册'),
                    array('sum_money', '结算金额'),
                    array('remark', '备注'),
                    array('status', '打款状态'),
                );
                $Settlement = new Settlement();
                $xlsData    = $Settlement
                    ->where($map)
                    ->select();
                foreach ($xlsData as $k => &$v) {
                    if ($v['status'] == 1) {
                        $v['status'] = '未打款';
                    }
                    if ($v['status'] == 2) {
                        $v['status'] = '已打款';
                    }
                    if ($v['status'] == 3) {
                        $v['status'] = '审核中';
                    }
                    if ($v['status'] == 4) {
                        $v['status'] = '拒绝打款';
                    }
                    $v['remark']      = $v['remark'] . ';' . $v['violation_remark'];
                    $v['create_time'] = date("Y-m-d H:i:s", $v['create_time']);
                    $v['record_date'] = $v['starttime'] . '至' . $v['endtime'];
                }
                break;
            case 4:
                $xlsName       = "预结算单";
                $xlsCell       = array(
                    array('record_date', '日期'),
                    array('promote_account', '推广员'),
                    array('promote_1_account', '一级推广员'),
                    array('package_name', '游戏名称'),
                    array('proportion', '分成比例(%)'),
                    array('total_money', '总充值'),
                    array('total_number', '总注册'),
                    array('sum_money', '预结算金额'),
                    array('event_money', '事件金额'),
                    array('remark', '事件原因'),
                    array('status', '状态'),
                    array('jies_type', '结算类型'),
                );
                $SettlementPre = new SettlementPre();
                $xlsData       = $SettlementPre
                    ->where($map)
                    ->select();
                foreach ($xlsData as $k => &$v) {
                    if ($v['status'] == 1) {
                        $v['status'] = '未结算';
                    }
                    if ($v['status'] == 2) {
                        $v['status'] = '结算中';
                    }
                    if ($v['status'] == 3) {
                        $v['status'] = '已结算';
                    }
                    if ($v['jies_type'] == 1) {
                        $v['jies_type'] = '周结';
                    }
                    if ($v['status'] == 2) {
                        $v['jies_type'] = '日结';
                    }
                }
                break;
            case 5:
                $xlsName             = "对账单";
                $xlsCell             = array(
                    array('settlement_number', '结算单号'),
                    array('promote_account', '结算推广员'),
                    array('promote_1_account', '一级推广员'),
                    array('sum_money', '结算金额'),
                    array('s_account_name', '银行账户名称'),
                    array('s_account', '银行账户'),
                    array('s_bank_account', '开户行'),
                    array('create_time', '生成时间'),
                    array('end_time', '审核时间'),
                    array('status', '状态'),
                );
                $SettlementStatement = new SettlementStatement();
                $xlsData             = $SettlementStatement
                    ->where($map)
                    ->select();
                foreach ($xlsData as $k => &$v) {
                    if ($v['status'] == -1) {
                        $v['status'] = '未申请';
                    }
                    if ($v['status'] == 0) {
                        $v['status'] = '申请中';
                    }
                    if ($v['status'] == 1) {
                        $v['status'] = '已打款';
                    }
                    if ($v['status'] == 2) {
                        $v['status'] = '已驳回';
                    }
                    $v['create_time'] = date("Y-m-d H:i:s", $v['create_time']);
                    $v['end_time']    = date("Y-m-d H:i:s", $v['end_time']);
                }
                break;
            case 6:
                $xlsName             = "违规事件";
                $xlsCell             = array(
                    array('record_date', '日期'),
                    array('package_account', '处罚游戏'),
                    array('server_role', '区服角色'),
                    array('role_id', '角色id'),
                    array('result', '交易查询'),
                    array('ascription', '归属'),
                    array('promote_account', '处罚推广员'),
                    array('jies_promote_account', '结算推广员'),
                    array('event_money', '事件金额'),
                    array('remark', '处理结果'),
                    array('jies_status', '结算状态'),
                    array('jies_time', '结算时间'),
                    array('status', '状态'),
                    array('type', '结算类型'),
                    array('update_time', '更新时间'),
                );
                $SettlementViolation = new SettlementViolation();
                $xlsData             = $SettlementViolation
                    ->where($map)
                    ->select();
                foreach ($xlsData as $k => &$v) {
                    if ($v['status'] == 0) {
                        $v['status'] = '未确认';
                    }
                    if ($v['status'] == 1) {
                        $v['status'] = '已确认';
                    }
                    if ($v['type'] == 0) {
                        $v['type'] = '非日结';
                    }
                    if ($v['type'] == 1) {
                        $v['type'] = '日结';
                    }
                    if ($v['jies_status'] == 0) {
                        $v['jies_status'] = '未结算';
                    }
                    if ($v['jies_status'] == 1) {
                        $v['jies_status'] = '已结算';
                    }
                    $v['update_time'] = date("Y-m-d H:i:s", $v['update_time']);
                }
                break;
            default:
                $xlsName = $xlsCell = $xlsData = [];
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * Desc : 后台游戏数据中心导出
     * User : steven
     * Date : 2024-01-09 14:28
     * @param $type
     * @param $map
     * @throws Exception
     */
    public function adminGameInfoExport($type, $map)
    {
        switch ($type) {
            case 1:
                $xlsName = "游戏数据总览";
                $xlsCell = array(
                    array('id', 'ID'),
                    array('day_time', '日期'),
                    array('game_name', '游戏名称'),
                    array('account_num', '新增账号数'),
                    array('role_num', '新增创角账号数'),
                    array('role_rate', '创角率(单位:%)'),
                    array('role_num_all', '新增角色数'),
                    array('active_account_num', '活跃账号数'),
                    array('pay_account_num', '付费账号数'),
                    array('active_account_rate', '活跃账号付费率(单位:%)'),
                    array('pay_amount', '付费金额'),
                    array('arpu', 'arpu'),
                    array('arppu', 'arppu'),
                    array('all_pay_amount', '总付费金额'),
                    array('all_account_num', '总账号数'),
                    array('all_pay_account_num', '总付费账号数'),
                    array('new_account_num', '新增付费账号数'),
                    array('new_account_rate', '新增付费账号率(单位:%)'),
                    array('new_account_amount', '新增账号当日付费金额'),
                );
                $xlsData = (new BoxGameDataAllLog())
                    ->where($map)
                    ->order('day_time', 'desc')
                    ->order('box_game_id', 'desc')
                    ->select();
                foreach ($xlsData as $key => &$val) {
                    $game_name        = (new BoxGame())->where('id', '=', $val['box_game_id'])->field('game_name')->find();
                    $val['game_name'] = $game_name['game_name'];
                    $date             = new DateTime($val['day_time']);
                    $val['day_time']  = $date->format('Y-m-d'); // 输出：2023-12-11
                    if (!empty($val['all_pay_amount']) && $val['all_account_num']) {
                        $val['arpu'] = sprintf("%.2f", $val['all_pay_amount'] / $val['all_account_num']);
                    } else {
                        $val['arpu'] = 0;
                    }

                    if (!empty($val['all_pay_amount']) && $val['all_pay_account_num']) {
                        $val['arppu'] = sprintf("%.2f", $val['all_pay_amount'] / $val['all_pay_account_num']);
                    } else {
                        $val['arppu'] = 0;
                    }
                }
                break;
            case 2:
                $xlsName = "流失分析";
                $xlsCell = array(
                    array('id', 'ID'),
                    array('day_time', '日期'),
                    array('game_name', '游戏名称'),
                    array('day_type', '阶梯时间跨度'),
                    array('account_pay_num', '付费用户数'),
                    array('amount_1', '1~10元流失用户'),
                    array('amount_10', '1~10元流失用户'),
                    array('amount_100', '100~1000流失用户数'),
                    array('amount_1000', '1000~10000流失用户数'),
                    array('amount_10000', '10000以上流失用户数'));
                $xlsData = (new BoxGameMemberLostLog())
                    ->where($map)
                    ->order('day_time', 'desc')
                    ->order('box_game_id', 'desc')
                    ->select();
                foreach ($xlsData as $key => &$val) {
                    $game_name        = (new BoxGame())->where('id', '=', $val['box_game_id'])->field('game_name')->find();
                    $val['game_name'] = $game_name['game_name'];
                    $date             = new DateTime($val['day_time']);
                    $val['day_time']  = $date->format('Y-m-d'); // 输出：2023-12-11
                    switch ($val['day_type']) {
                        case 1:
                            $val['day_type'] = '1~7天';
                            break;
                        case 2:
                            $val['day_type'] = '7~14天';
                            break;
                        case 3:
                            $val['day_type'] = '14~30天';
                            break;
                        case 4:
                            $val['day_type'] = '30天以上';
                            break;
                        default:
                            $val['day_type'] = '---';
                    }
                }
                break;
            case 3:
                $xlsName = "LTV统计";
                $xlsCell = array(
                    array('id', 'ID'),
                    array('day_time', '日期'),
                    array('game_name', '游戏名称'),
                    array('account_num', '注册账号数'),
                    array('pay_account_amount', '首日付费金额'),
                );
                $cell    = [];
                for ($i = 2; $i <= 60; $i++) {
                    $cell[$i + 3] = array('d' . $i, $i . '日LTV');
                }
                $xlsCell = $xlsCell + $cell;
                $xlsData = (new BoxGameMemberLtvLog())
                    ->where($map)
                    ->order('day_time', 'desc')
                    ->order('box_game_id', 'desc')
                    ->select();
                foreach ($xlsData as $key => &$val) {
                    $game_name        = (new BoxGame())->where('id', '=', $val['box_game_id'])->field('game_name')->find();
                    $val['game_name'] = $game_name['game_name'];
                    $date             = new DateTime($val['day_time']);
                    $val['day_time']  = $date->format('Y-m-d'); // 输出：2023-12-11
                }

                break;
            case 4:
                $xlsName = "用户付费率";
                $xlsCell = array(
                    array('id', 'ID'),
                    array('day_time', '日期'),
                    array('game_name', '游戏名称'),
                    array('account_num', '注册账号数'),
                    array('pay_account_num', '首日付费用户数'),
                );
                $cell    = [];
                for ($i = 2; $i <= 60; $i++) {
                    $cell[$i + 3] = array('d' . $i, $i . '日付费率');
                }
                $xlsCell = $xlsCell + $cell;
                $xlsData = (new BoxGamePayFirstLog)
                    ->where($map)
                    ->order('day_time', 'desc')
                    ->order('box_game_id', 'desc')
                    ->select();
                foreach ($xlsData as $key => &$val) {
                    $game_name        = (new BoxGame())->where('id', '=', $val['box_game_id'])->field('game_name')->find();
                    $val['game_name'] = $game_name['game_name'];
                    $date             = new DateTime($val['day_time']);
                    $val['day_time']  = $date->format('Y-m-d'); // 输出：2023-12-11
                    if (!empty($val['account_num'])) {
                        for ($i = 2; $i <= 60; $i++) {
                            $val['d' . $i] = round($val['d' . $i] / $val['account_num'] * 100, 2);
                        }
                    }
                }
                break;
            case 5:
                $xlsName = "付费用户留存";
                $xlsCell = array(
                    array('id', 'ID'),
                    array('day_time', '日期'),
                    array('game_name', '游戏名称'),
                );
                $cell    = [];
                for ($i = 3; $i <= 61; $i++) {
                    $cell[$i] = array('d' . ($i - 1), ($i - 1) . '日付费率留存');
                }
                $xlsCell = $xlsCell + $cell;
                $xlsData = (new BoxGameMemberPayRemainLog)
                    ->where($map)
                    ->order('day_time', 'desc')
                    ->order('box_game_id', 'desc')
                    ->select();
                foreach ($xlsData as $key => &$val) {
                    $game_name        = (new BoxGame())->where('id', '=', $val['box_game_id'])->field('game_name')->find();
                    $val['game_name'] = $game_name['game_name'];
                    $date             = new DateTime($val['day_time']);
                    $val['day_time']  = $date->format('Y-m-d'); // 输出：2023-12-11
                    if (!empty($val['account_pay_num'])) {
                        for ($i = 2; $i <= 60; $i++) {
                            $val['d' . $i] = round($val['d' . $i] / $val['account_pay_num'] * 100, 2);
                        }
                    }
                }
                break;
            case 6:
                $xlsName = "用户留存";
                $xlsCell = array(
                    array('id', 'ID'),
                    array('day_time', '日期'),
                    array('game_name', '游戏名称'),
                );
                $cell    = [];
                for ($i = 3; $i <= 61; $i++) {
                    $cell[$i] = array('d' . ($i - 1), ($i - 1) . '日用户留存');
                }
                $xlsCell = $xlsCell + $cell;
                $xlsData = (new BoxGameMemberRemainLog)
                    ->where($map)
                    ->order('day_time', 'desc')
                    ->order('box_game_id', 'desc')
                    ->select();
                foreach ($xlsData as $key => &$val) {
                    $game_name        = (new BoxGame())->where('id', '=', $val['box_game_id'])->field('game_name')->find();
                    $val['game_name'] = $game_name['game_name'];
                    $date             = new DateTime($val['day_time']);
                    $val['day_time']  = $date->format('Y-m-d'); // 输出：2023-12-11
                    if (!empty($val['account_num'])) {
                        for ($i = 2; $i <= 60; $i++) {
                            $val['d' . $i] = round($val['d' . $i] / $val['account_num'] * 100, 2);
                        }
                    }

                }
                break;
            default:
                $xlsName = $xlsCell = $xlsData = [];
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    public function adminDataStatisticsExport($type, $param)
    {
        $map = [];
        switch ($type) {
            case 1:
                if (!empty($param['time_range'])) {
                    $map[] = ['date', '>=', $param['time_range'][0]];
                    $map[] = ['date', '<=', $param['time_range'][1]];
                }
                $xlsName = "用户留存";
                $xlsCell = array(
                    array('id', 'ID'),
                    array('date', '日期'),
                );
                $cell    = [];
                for ($i = 2; $i <= 45; $i++) {
                    $cell[$i] = array($i . 'day', $i . '日用户留存');
                }
                $xlsCell = $xlsCell + $cell;

                $memberRemain = new BoxMemberRemain();
                $xlsData      = $memberRemain
                    ->where($map)
                    ->order('date', 'desc')
                    ->select();
                foreach ($xlsData as $key => &$value) {
                    //获取当日注册的用户
                    for ($i = 2; $i <= 45; $i++) {
                        //当前的天数往前推
                        $value[$i . 'day'] = empty($value['reg_num']) ? 0 : round($value[$i . 'day'] / $value['reg_num'] * 100, 2);
                    }
                }
                break;
            case 2:
                $xlsName = "弹窗广告数据";
                $xlsCell = array(
                    array('id', 'ID'),
                    array('ctime', '时间'),
                    array('title', '广告名称'),
                    array('pv', 'PV'),
                    array('uv', 'UV'),
                );
                //获取当前显示的banner_id
                $where   = [
                    'show_type' => !empty($param['show_type']) ? $param['show_type'] : [1, 2],
                    'mark'      => 1,
                ];
                $xlsData = (new BoxAd())
                    ->where($where)
                    ->field('id,title,show_type')
                    ->order('id', 'desc')
                    ->select();
                $map     = [];
                $time    = date("Y-m-d ");
                if (!empty($param['time_range'])) {
                    $map[] = ['ctime', '>=', $param['time_range'][0]];
                    $map[] = ['ctime', '<=', $param['time_range'][1]];
                } else {
                    $map[] = ['ctime', '>=', $time . '00:00:01'];
                    $map[] = ['ctime', '<=', $time . '23:59:59'];
                }

                foreach ($xlsData as $key => $value) {
                    $AdModel                = new BoxAdLog();
                    $xlsData[$key]['pv']    = $AdModel->where($map)->where('ad_id', $value['id'])->count() ?? 0;
                    $xlsData[$key]['uv']    = $AdModel->where($map)->where('ad_id', $value['id'])->group('member_id')->count() ?? 0;
                    $xlsData[$key]['ctime'] = $map[0][2] . '  ~  ' . $map[1][2];

                }
                break;
            case 3:
                $xlsName = "轮播广告数据";
                $xlsCell = array(
                    array('id', 'ID'),
                    array('ctime', '时间'),
                    array('title', '广告名称'),
                    array('pv', 'PV'),
                    array('uv', 'UV'),
                );
                //获取当前显示的banner_id
                $where   = [
                    'show_type' => 3,
                    'mark'      => 1,
                ];
                $xlsData = (new BoxAd())
                    ->where($where)
                    ->field('id,title')
                    ->order('id', 'desc')
                    ->select();
                $map     = [];
                $time    = date("Y-m-d ");

                if (!empty($param['time_range'])) {
                    $map[] = ['ctime', '>=', $param['time_range'][0]];
                    $map[] = ['ctime', '<=', $param['time_range'][1]];
                } else {
                    $map[] = ['ctime', '>=', $time . '00:00:01'];
                    $map[] = ['ctime', '<=', $time . '23:59:59'];
                }

                foreach ($xlsData as $key => $value) {
                    $xlsData[$key]['pv']    = (new BoxBannerLog())->where($map)->where('ad_id', $value['id'])->count() ?? 0;
                    $xlsData[$key]['uv']    = (new BoxBannerLog())->where($map)->where('ad_id', $value['id'])->group('member_id')->count() ?? 0;
                    $xlsData[$key]['ctime'] = $map[0][2] . '  ~  ' . $map[1][2];
                }
                break;
            case 4:
                $xlsName = "设备留存";
                $xlsCell = array(
                    array('id', 'ID'),
                    array('time_day', '日期'),
                );
                $cell    = [];
                for ($i = 2; $i <= 45; $i++) {
                    $cell[$i] = array($i . 'day', $i . '日设备留存');
                }
                $xlsCell = $xlsCell + $cell;
                if (!empty($param['time_range'])) {
                    $map[] = ['time_day', '>=', $param['time_range'][0]];
                    $map[] = ['time_day', '<=', $param['time_range'][1]];
                }
                $deviceRemain = new BoxDeviceRemain();
                $xlsData      = $deviceRemain
                    ->where($map)
                    ->order('time_day', 'desc')
                    ->select();
                foreach ($xlsData as $key => &$value) {
                    //获取当日注册的用户
                    for ($i = 2; $i <= 45; $i++) {
                        $value[$i . 'day'] = empty($value['device_num']) ? 0 : round($value[$i . 'day'] / $value['device_num'] * 100, 2);
                    }
                }
                break;
            case 5:
                $xlsName = "数据总览";
                $xlsCell = array(
                    array('id', 'ID'),
                    array('execute_time', '日期'),
                    array('activate_number', '激活人数'),
                    array('account_number', '账号注册数'),
                    array('device_number', '设备注册数'),
                    array('amount', '充值金额'),
                    array('active_time', '平均活跃时长'),
                    array('start_number', '平均启动次数'),
                );
                if (!empty($param['time_range'])) {
                    $map[] = ['execute_time', '>=', strtotime($param['time_range'][0])];
                    $map[] = ['execute_time', '<=', strtotime($param['time_range'][1])];
                }
                $xlsData = (new BoxDataOverview())
                    ->where($map)
                    ->order('execute_time', 'desc')
                    ->select();
                foreach ($xlsData as $key => &$value) {
                    $value['execute_time'] = date('Y-m-d', $value['execute_time']);
                }
                break;
            default:
                $xlsName = $xlsCell = $xlsData = [];
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * 推广数据总览
     * @param [type] $map
     * @return void
     */
    public function expPromotePackageAll($map, $promoteChild = [])
    {
        $xlsName                  = "推广数据总览";
        $xlsCell                  = array(
            array('record_date', '日期'),
            array('sdk_version', '客户端'),
            array('register_num', '新增用户数'),
            array('pay_num', '付费用户数'),
            array('login_num', '活跃用户数'),
            array('pay_amount', '付费总额'),
            array('remain_d2', '次日留存'),
            array('remain_d3', '3日留存'),
            array('remain_d7', '7日留存'),
            array('remain_d15', '15日留存'),
            array('rate_d1', '当日付费率'),
            array('rate_d3', '3日付费率'),
            //array('promote_1_account', '所属一级推广员'),
            array('rate_d7', '7日付费率'),
            array('rate_d15', '15日付费率'),
            array('arpu', 'ARPU(单位:元)'),
            array('active_arpu', '活跃ARPU(单位:元)'),
            array('arppu', 'APRPU(单位:元)'),

        );
        $PromotePackageRemainList = (new PromotePackageStats)->field(" record_date, sdk_version, sum(register_num) as register_num, sum(pay_num) as pay_num, sum(login_num) as login_num, sum(pay_amount) as pay_amount")->where($map)->group("record_date,sdk_version")->order('record_date', 'desc')->order('package_id', 'desc');
        $xlsData                  = $PromotePackageRemainList->select();

        $version = [1 => '安卓', 2 => '苹果', 3 => 'h5', 4 => 'pc'];
        foreach ($xlsData as $key => &$val) {
            $_map = [];
            if (!empty($promoteChild)) {
                $_map[] = ['promote_id', 'in', $promoteChild];
            }
            $_map[] = ['sdk_version', '=', $val['sdk_version']];
            $_map[] = ['record_date', '=', $val["record_date"]];
            $remain = (new PromotePackageRemain)->field("sum(register_num) as register_num, sum(d2) as d2,sum(d3) as d3,sum(d7) as d7, sum(d15) as d15")->where($_map)->find();
            //$val['remain_register_num'] = $remain['register_num'];
            $val['remain_d2']  = $val['remain_d3'] = $val['remain_d7'] = $val['remain_d15'] = 0;
            $val['remain_d2']  = empty($remain['register_num']) ? "0%" : round($remain['d2'] / $remain['register_num'] * 100, 2) . "%";
            $val['remain_d3']  = empty($remain['register_num']) ? "0%" : round($remain['d3'] / $remain['register_num'] * 100, 2) . "%";
            $val['remain_d7']  = empty($remain['register_num']) ? "0%" : round($remain['d7'] / $remain['register_num'] * 100, 2) . "%";
            $val['remain_d15'] = empty($remain['register_num']) ? "0%" : round($remain['d15'] / $remain['register_num'] * 100, 2) . "%";
            $rate              = (new PromotePackagePayRate)->field("sum(register_num) as register_num, sum(d1) as d1,sum(d3) as d3,sum(d7) as d7, sum(d15) as d15")->where($_map)->find();
            //$val['rate_register_num'] = $rate['register_num'];
            $val['rate_d1']  = $val['rate_d3'] = $val['rate_d7'] = $val['rate_d15'] = 0;
            $val['rate_d1']  = empty($rate['register_num']) ? "0%" : round($rate['d1'] / $rate['register_num'] * 100, 2) . "%";
            $val['rate_d3']  = empty($rate['register_num']) ? "0%" : round($rate['d3'] / $rate['register_num'] * 100, 2) . "%";
            $val['rate_d7']  = empty($rate['register_num']) ? "0%" : round($rate['d7'] / $rate['register_num'] * 100, 2) . "%";
            $val['rate_d15'] = empty($rate['register_num']) ? "0%" : round($rate['d15'] / $rate['register_num'] * 100, 2) . "%";

            $val['arpu']        = $val['register_num'] > 0 ? round($val['pay_amount'] / $val['register_num'], 2) : 0;
            $val['active_arpu'] = $val['login_num'] > 0 ? round($val['pay_amount'] / $val['login_num'], 2) : 0;
            $val['arppu']       = $val['pay_num'] > 0 ? round($val['pay_amount'] / $val['pay_num'], 2) : 0;
            $val['sdk_version'] = $version[$val['sdk_version']];
            //$val['promote_realname'] = isset($promote[$val['promote_id']]) ? $promote[$val['promote_id']]: "";
            //$val['package_name'] = isset($package[$val['package_id']]) ? $package[$val['package_id']] : "";
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * 推广游戏留存统计
     * @param [type] $map
     * @return void
     */
    public function expPromotePackageRemain($map)
    {
        $xlsName = "推广游戏留存统计";
        $xlsCell = array(
            0 => array('record_date', '日期'),
            1 => array('promote_realname', '推广渠道'),
            2 => array('register_num', '新增用户数(设备)'),
        );
        for ($i = 3; $i <= 61; $i++) {
            $xlsCell[$i] = array('d' . ($i - 1), 'D' . ($i - 1));
        }
        $PromotePackageRemainList = (new PromotePackageRemain)->where($map)->order('record_date', 'desc')->order('package_id', 'desc');
        $xlsData                  = $PromotePackageRemainList->select();
        $promote_ids              = array_column($xlsData->toArray(), 'promote_id');
        $promote                  = (new UserPromote())->whereIn('id', $promote_ids)->column('realname', 'id');

        foreach ($xlsData as $key => &$val) {
            $val['promote_realname'] = isset($promote[$val['promote_id']]) ? $promote[$val['promote_id']] : "";
            //$val['package_name'] = isset($package[$val['package_id']]) ? $package[$val['package_id']] : "";
            for ($i = 2; $i <= 60; $i++) {
                $val['d' . $i] = empty($val['register_num']) ? "0%" : round($val['d' . $i] / $val['register_num'] * 100, 2) . "%";
            }
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * 推广员游戏付费用户留存统计
     */
    public function expPromotePackagePayRemain($map)
    {
        $xlsName = "推广员游戏付费用户留存统计";
        $xlsCell = array(
            0 => array('record_date', '日期'),
            1 => array('promote_realname', '推广渠道'),
            2 => array('pay_num', '新增用户数(设备)'),
        );
        for ($i = 3; $i <= 61; $i++) {
            $xlsCell[$i] = array('d' . ($i - 1), 'D' . ($i - 1));
        }
        $PromotePackagePayRemainList = (new PromotePackagePayRemain)->where($map)->order('record_date', 'desc')->order('package_id', 'desc');
        $xlsData                     = $PromotePackagePayRemainList->select();
        $promote_ids                 = array_column($xlsData->toArray(), 'promote_id');
        $promote                     = (new UserPromote())->whereIn('id', $promote_ids)->column('realname', 'id');

        foreach ($xlsData as $key => &$val) {
            $val['promote_realname'] = isset($promote[$val['promote_id']]) ? $promote[$val['promote_id']] : "";
            for ($i = 2; $i <= 60; $i++) {
                $val['d' . $i] = empty($val['pay_num']) ? "0%" : round($val['d' . $i] / $val['pay_num'] * 100, 2) . "%";
            }
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * 推广员游戏付费率
     * @param [type] $map
     * @return void
     */
    public function expPromotePackagePayRate($map)
    {
        $xlsName = "推广员游戏付费率";
        $xlsCell = array(
            0 => array('record_date', '日期'),
            1 => array('promote_realname', '推广渠道'),
            2 => array('register_num', '新增用户数(设备)'),
        );
        for ($i = 3; $i <= 61; $i++) {
            $xlsCell[$i] = array('d' . ($i - 1), 'D' . ($i - 1));
        }

        $PromotePackagePayRateList = (new PromotePackagePayRate)->where($map)->order('record_date', 'desc')->order('package_id', 'desc');
        $xlsData                   = $PromotePackagePayRateList->select();
        $promote_ids               = array_column($xlsData->toArray(), 'promote_id');
        $promote                   = (new UserPromote())->whereIn('id', $promote_ids)->column('realname', 'id');

        foreach ($xlsData as $key => &$val) {
            $val['promote_realname'] = isset($promote[$val['promote_id']]) ? $promote[$val['promote_id']] : "";
            for ($i = 1; $i <= 60; $i++) {
                $val['_d' . $i] = empty($val['register_num']) ? "0%" : round($val['d' . $i] / $val['register_num'] * 100, 2) . "%";
            }
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * 广告投放统计
     * @param [type] $map
     * @return void
     */
    public function boxAdStatistics($map)
    {
        $xlsName                   = "广告投放统计";
        $xlsCell                   = array(
            0  => array('day_time', '日期'),
            1  => array('delivery_channel_name', '投放渠道'),
            2  => array('name', '投放产品名称'),
            3  => array('product_type', '产品类型 1=ios ,2=安卓'),
            4  => array('adid', '广告位ID'),
            5  => array('ad_name', '广告位名称'),
            6  => array('click_num', '广告点击量'),
            7  => array('active_num', '广告激活数'),
            8  => array('register_num', '广告注册数'),
            9  => array('arpu', 'arpu:用户的平均付费金额'),
            10 => array('today_pay_people_num', '当日付费人数'),
            11 => array('today_pay_amount', '当日付费金额'),
            12 => array('today_payment_rate', '当日付费率'),
            13 => array('pay_total_people_num', '付费人数'),
            14 => array('pay_subscriber_rate', '付费率'),
            15 => array('user_pay_amount', '用户付费金额'),
        );
        $PromotePackagePayRateList = (new BoxAdStatisticalDataLog())->where($map)->order('id asc');
        $xlsData                   = $PromotePackagePayRateList->select();
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * 云挂机订单导出
     * @param [type] $map
     * @return void
     */
    public function BoxYunOrder($where)
    {
        $xlsName = "云挂机订单导出";
        $xlsCell = array(
            array('id', '序号'),
            array('id', '用户ID'),
            array('nickname', '用户昵称'),
            array('order_type', '产品类型: 1=新增，2=续时, 3=免费领取 , 4=CDK新增 , 5=CDK续费'),
            array('good_name', '商品名称'),
            array('good_price', '商品售价(单位:元)'),
            array('num', '商品数量'),
            array('pay_amount', '支付金额(单位:元)'),
            array('order_num', '订单编号'),
            array('order_status', '订单状态，-1=实付金额校验错误, 0=已取消，1=待支付，2=已支付待发货，3=已完成'),
            array('pay_status', '支付状态，0、未支付，1、已支付'),
            array('create_time', '下单时间'),
            array('pay_time', '支付时间'),
        );
        $xlsData = (new BoxYunOrder)->alias('a')
            ->join('cq_box_yun_goods b', 'a.goods_id = b.id')
            ->join('cq_member c', 'a.member_id = c.id')
            ->field('a.id,a.member_id,a.status order_status,a.order_num,a.yun_type order_type,a.pay_status,a.pay_time,a.create_time,b.name good_name,b.type good_type,b.price good_price,c.nickname,a.num,a.pay_amount')
            ->order('a.id', 'desc')
            ->where('a.mark', '=', 1)
            ->where($where)
            ->select();
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * 导出数据总览
     * @param [type] $map
     * @return void
     */
    public function overviewExport($param, $map)
    {
        $xlsName = "数据总览";
        $xlsCell = array(
            array('day_time', '日期'),
            array('platform', '平台'),
            array('device_num', '新增激活数'),
            array('reg_device_num', '新增设备注册数'),
            array('register_num', '注册账号数'),
            array('device_register_rate', '设备注册率'),
            array('pay_amount', '充值金额'),
            array('pay_people_num', '账号付费数'),
            array('arppu', '账号ARPPU'),
            array('pay_rate', '账号付费率'),
            array('dau', '活跃账号数'),
            array('avg_start_up', '平均启动次数'),
            array('new_pay_num', '新增账号付费人数'),
            array('new_pay_amount', '新增账号付费金额'),
            array('new_pay_rate', '新增账号付费率'),
            array('d2', '2日留存'),
            array('d3', '3日留存'),
            array('d7', '7日留存'),
        );

        if (!empty($param['platform']) && $param['platform'] == 1) {
            $xlsCell1 = array(
                array('cost', '成本'),
                array('average_cost', '平均成本'),
            );
            $xlsCell  = array_merge($xlsCell, $xlsCell1);
        }

        if (empty($param['platform'])) {
            $list = (new BoxStatisticsMemberOverviewAllLog())
                ->field('day_time,device_num,reg_device_num,register_num,pay_amount,pay_people_num,dau,start_up_num,up_device_num,new_pay_num,new_pay_amount')
                ->where($map)
                ->order('day_time', 'desc')
                ->select()->toArray();
            //获取数据总数
            $count = (new BoxStatisticsMemberOverviewAllLog())->where($map)->count();
        } else {
            $list = (new BoxStatisticsMemberOverviewLog())
                ->field('day_time,platform,device_num,reg_device_num,register_num,pay_amount,pay_people_num,dau,start_up_num,up_device_num,new_pay_num,new_pay_amount,cost')
                ->where($map)
                ->order('day_time', 'desc')
                ->select()->toArray();
            //获取数据总数
            $count = (new BoxStatisticsMemberOverviewLog())->where($map)->count();
        }
        $MemberRemainLog = (new BoxMemberRemainLog())
            ->field('day_time,sum(account_num) as account_num,sum(d2) as d2,sum(d3) as d3,sum(d7) as d7')
            ->where($map)
            ->order('day_time', 'desc')
            ->group('day_time')
            ->select()->toArray();
        $MemberRemainLog = array_column($MemberRemainLog ?? [], null, 'day_time');


        $xlsData = array();
        if (!empty($list)) {
            $device_num_all           = 0;
            $reg_device_num_all       = 0;
            $register_num_all         = 0;
            $device_register_rate_all = 0;
            $pay_amount_all           = 0;
            $pay_people_num_all       = 0;
            $arppu_all                = 0;
            $pay_rate_all             = 0;
            $dau_all                  = 0;
            $avg_start_up_all         = 0;
            $new_pay_num_all          = 0;
            $new_pay_amount_all       = 0;
            $new_pay_rate_all         = 0;
            $d2_all                   = 0;
            $d3_all                   = 0;
            $d7_all                   = 0;
            foreach ($list as $key => $item) {
                if (isset($item['platform']) && $item['platform'] == 1) {
                    $cost                          = $item['cost'] ?? 0;
                    $xlsData[$key]['cost']         = $cost;
                    $xlsData[$key]['average_cost'] = $item['register_num'] > 0 ? round($cost / $item['register_num'], 2) : 0;
                }
                $xlsData[$key]['day_time']             = date("Y-m-d", strtotime($item['day_time']));//日期
                $xlsData[$key]['platform']             = empty($param['platform']) ? '全部' : BoxReportMemberOverviewService::$platform[$param['platform']];//新增激活数
                $xlsData[$key]['device_num']           = $item['device_num'];//新增激活数
                $xlsData[$key]['reg_device_num']       = $item['reg_device_num'];//新增设备注册数
                $xlsData[$key]['register_num']         = $item['register_num'];//注册账号数
                $xlsData[$key]['device_register_rate'] = empty($item['device_num']) ? "0%" : round($item['register_num'] / $item['device_num'] * 100) . "%";//设备注册率
                $xlsData[$key]['pay_amount']           = $item['pay_amount'];//充值金额
                $xlsData[$key]['pay_people_num']       = $item['pay_people_num'];//账号付费数
                $xlsData[$key]['arppu']                = empty($item['pay_people_num']) ? '0' : round($item['pay_amount'] / $item['pay_people_num'], 2) ?? '0';//账号ARPPU
                $xlsData[$key]['pay_rate']             = empty($item['dau']) ? '0%' : round($item['pay_people_num'] / $item['dau'] * 100, 2) . "%";//账号付费率
                $xlsData[$key]['dau']                  = $item['dau'];//活跃账号数
                $xlsData[$key]['avg_start_up']         = empty($item['up_device_num']) ? '0' : round($item['start_up_num'] / $item['up_device_num'], 2) ?? '0';//平均启动次数
                $xlsData[$key]['new_pay_num']          = $item['new_pay_num'];//新增账号付费人数
                $xlsData[$key]['new_pay_amount']       = $item['new_pay_amount'];//新增账号付费金额
                $xlsData[$key]['new_pay_rate']         = empty($item['register_num']) ? '0%' : round($item['new_pay_num'] / $item['register_num'] * 100, 2) . "%";//新增账号付费率
                $xlsData[$key]['d2']                   = empty($MemberRemainLog[$item['day_time']]['d2']) ? "0%" : round($MemberRemainLog[$item['day_time']]['d2'] / $MemberRemainLog[$item['day_time']]['account_num'] * 100) . "%";//2日留存
                $xlsData[$key]['d3']                   = empty($MemberRemainLog[$item['day_time']]['d3']) ? "0%" : round($MemberRemainLog[$item['day_time']]['d3'] / $MemberRemainLog[$item['day_time']]['account_num'] * 100) . "%";//3日留存
                $xlsData[$key]['d7']                   = empty($MemberRemainLog[$item['day_time']]['d7']) ? "0%" : round($MemberRemainLog[$item['day_time']]['d7'] / $MemberRemainLog[$item['day_time']]['account_num'] * 100) . "%";//7日留存

                $device_num_all           += $item['device_num'];//新增激活数
                $reg_device_num_all       += $item['reg_device_num'];//新增激活数
                $register_num_all         += $item['register_num'];//注册账号数
                $device_register_rate_all += empty($item['device_num']) ? '0' : round($item['register_num'] / $item['device_num'] * 100) ?? '0';//设备注册率
                $pay_amount_all           += $item['pay_amount'];//充值金额
                $pay_people_num_all       += $item['pay_people_num'];//账号付费数
                $arppu_all                += empty($item['pay_people_num']) ? '0' : round($item['pay_amount'] / $item['pay_people_num'], 2) ?? '0';//账号ARPPU
                $pay_rate_all             += empty($item['dau']) ? '0' : round($item['pay_people_num'] / $item['dau'] * 100, 2) ?? '0';//账号付费率
                $dau_all                  += $item['dau'];//活跃账号数
                $avg_start_up_all         += empty($item['up_device_num']) ? '0' : round($item['start_up_num'] / $item['up_device_num'], 2) ?? '0';//平均启动次数
                $new_pay_num_all          += $item['new_pay_num'];//新增账号付费人数
                $new_pay_amount_all       += $item['new_pay_amount'];//新增账号付费金额
                $new_pay_rate_all         += empty($item['register_num']) ? '0' : round($item['new_pay_num'] / $item['register_num'] * 100, 2) ?? '0';//新增账号付费率
                $d2_all                   += empty($MemberRemainLog[$item['day_time']]['d2']) ? '0' : round($MemberRemainLog[$item['day_time']]['d2'] / $MemberRemainLog[$item['day_time']]['account_num'] * 100);//2日留存
                $d3_all                   += empty($MemberRemainLog[$item['day_time']]['d3']) ? '0' : round($MemberRemainLog[$item['day_time']]['d3'] / $MemberRemainLog[$item['day_time']]['account_num'] * 100);//3日留存
                $d7_all                   += empty($MemberRemainLog[$item['day_time']]['d7']) ? '0' : round($MemberRemainLog[$item['day_time']]['d7'] / $MemberRemainLog[$item['day_time']]['account_num'] * 100);//7日留存
            }
            $xlsData[$count]['day_time']             = '汇总';//日期
            $xlsData[$count]['platform']             = '-';//新增激活数
            $xlsData[$count]['device_num']           = $device_num_all;
            $xlsData[$count]['reg_device_num']       = $reg_device_num_all;
            $xlsData[$count]['register_num']         = $register_num_all;
            $xlsData[$count]['device_register_rate'] = round($device_register_rate_all / $count) . "%";
            $xlsData[$count]['pay_amount']           = $pay_amount_all;
            $xlsData[$count]['pay_people_num']       = $pay_people_num_all;
            $xlsData[$count]['arppu']                = round($arppu_all / $count, 2);
            $xlsData[$count]['pay_rate']             = round($pay_rate_all / $count, 2) . "%";
            $xlsData[$count]['dau']                  = $dau_all;
            $xlsData[$count]['avg_start_up']         = round($avg_start_up_all / $count, 2);
            $xlsData[$count]['new_pay_num']          = $new_pay_num_all;
            $xlsData[$count]['new_pay_amount']       = $new_pay_amount_all;
            $xlsData[$count]['new_pay_rate']         = round($new_pay_rate_all / $count, 2) . "%";
            $xlsData[$count]['d2']                   = round($d2_all / $count) . "%";
            $xlsData[$count]['d3']                   = round($d3_all / $count) . "%";
            $xlsData[$count]['d7']                   = round($d7_all / $count) . "%";
            $xlsData[$count]['cost']                 = 0;
            $xlsData[$count]['average_cost']         = 0;
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * @Notes:导出盒子用户留存
     * @Interface memberRemainExport
     * @param $map
     */
    public function memberRemainExport($map)
    {
        $xlsName = "用户留存";
        $xlsCell = array(
            0  => array('day_time', '日期'),
            1  => array('account_num', '注册账号数'),
            2  => array('d2', '2日留存'),
            3  => array('d3', '3日留存'),
            4  => array('d4', '4日留存'),
            5  => array('d5', '5日留存'),
            6  => array('d6', '6日留存'),
            7  => array('d7', '7日留存'),
            8  => array('d14', '14日留存'),
            9  => array('d30', '30日留存'),
            10 => array('d45', '45日留存'),
            11 => array('d60', '60日留存'),
            12 => array('d90', '90日留存'),
            13 => array('d120', '120日留存'),
            14 => array('d180', '180日留存'),
            15 => array('d360', '360日留存'),
        );


        $list = (new BoxMemberRemainLog())
            ->field('day_time,sum(account_num) as account_num, sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d45) as d45,sum(d60) as d60,sum(d90) as d90,sum(d120) as d120,sum(d180) as d180,sum(d360) as d360')
            ->where($map)
            ->order('day_time', 'desc');

        if (empty($param['platform'])) {
            $list->group('day_time');
        } else {
            $list->group('day_time,platform');
        }

        $xlsData = $list->select()->toArray();
        //获取数据总数
        $count = count($xlsData);

        if (!empty($xlsData)) {
            $account_num_all = 0;
            $d2_all          = 0;
            $d3_all          = 0;
            $d4_all          = 0;
            $d5_all          = 0;
            $d6_all          = 0;
            $d7_all          = 0;
            $d14_all         = 0;
            $d30_all         = 0;
            $d45_all         = 0;
            $d60_all         = 0;
            $d90_all         = 0;
            $d120_all        = 0;
            $d180_all        = 0;
            $d360_all        = 0;
            foreach ($xlsData as $key => &$item) {
                $account_num_all += $item['account_num'];
                if (!empty($item['account_num'])) {
                    $d2_all   += round($item['d2'] / $item['account_num'] * 100);
                    $d3_all   += round($item['d3'] / $item['account_num'] * 100);
                    $d4_all   += round($item['d4'] / $item['account_num'] * 100);
                    $d5_all   += round($item['d5'] / $item['account_num'] * 100);
                    $d6_all   += round($item['d6'] / $item['account_num'] * 100);
                    $d7_all   += round($item['d7'] / $item['account_num'] * 100);
                    $d14_all  += round($item['d14'] / $item['account_num'] * 100);
                    $d30_all  += round($item['d30'] / $item['account_num'] * 100);
                    $d45_all  += round($item['d45'] / $item['account_num'] * 100);
                    $d60_all  += round($item['d60'] / $item['account_num'] * 100);
                    $d90_all  += round($item['d90'] / $item['account_num'] * 100);
                    $d120_all += round($item['d120'] / $item['account_num'] * 100);
                    $d180_all += round($item['d180'] / $item['account_num'] * 100);
                    $d360_all += round($item['d360'] / $item['account_num'] * 100);
                }

                $item['day_time'] = date("Y-m-d", strtotime($item['day_time']));//日期
                $item['d2']       = empty($item['account_num']) ? "0%" : round($item['d2'] / $item['account_num'] * 100) . "%";//2日留存
                $item['d3']       = empty($item['account_num']) ? "0%" : round($item['d3'] / $item['account_num'] * 100) . "%";//2日留存
                $item['d4']       = empty($item['account_num']) ? "0%" : round($item['d4'] / $item['account_num'] * 100) . "%";//2日留存
                $item['d5']       = empty($item['account_num']) ? "0%" : round($item['d5'] / $item['account_num'] * 100) . "%";//2日留存
                $item['d6']       = empty($item['account_num']) ? "0%" : round($item['d6'] / $item['account_num'] * 100) . "%";//2日留存
                $item['d7']       = empty($item['account_num']) ? "0%" : round($item['d7'] / $item['account_num'] * 100) . "%";//2日留存
                $item['d14']      = empty($item['account_num']) ? "0%" : round($item['d14'] / $item['account_num'] * 100) . "%";//2日留存
                $item['d30']      = empty($item['account_num']) ? "0%" : round($item['d30'] / $item['account_num'] * 100) . "%";//2日留存
                $item['d45']      = empty($item['account_num']) ? "0%" : round($item['d45'] / $item['account_num'] * 100) . "%";//2日留存
                $item['d60']      = empty($item['account_num']) ? "0%" : round($item['d60'] / $item['account_num'] * 100) . "%";//2日留存
                $item['d90']      = empty($item['account_num']) ? "0%" : round($item['d90'] / $item['account_num'] * 100) . "%";//2日留存
                $item['d120']     = empty($item['account_num']) ? "0%" : round($item['d120'] / $item['account_num'] * 100) . "%";//2日留存
                $item['d180']     = empty($item['account_num']) ? "0%" : round($item['d180'] / $item['account_num'] * 100) . "%";//2日留存
                $item['d360']     = empty($item['account_num']) ? "0%" : round($item['d360'] / $item['account_num'] * 100) . "%";//2日留存

            }
            $xlsData[$count]['day_time']    = "汇总";
            $xlsData[$count]['account_num'] = $account_num_all;
            $xlsData[$count]['d2']          = round($d2_all / $count) . "%";
            $xlsData[$count]['d3']          = round($d3_all / $count) . "%";
            $xlsData[$count]['d4']          = round($d4_all / $count) . "%";
            $xlsData[$count]['d5']          = round($d5_all / $count) . "%";
            $xlsData[$count]['d6']          = round($d6_all / $count) . "%";
            $xlsData[$count]['d7']          = round($d7_all / $count) . "%";
            $xlsData[$count]['d14']         = round($d14_all / $count) . "%";
            $xlsData[$count]['d30']         = round($d30_all / $count) . "%";
            $xlsData[$count]['d45']         = round($d45_all / $count) . "%";
            $xlsData[$count]['d60']         = round($d60_all / $count) . "%";
            $xlsData[$count]['d90']         = round($d90_all / $count) . "%";
            $xlsData[$count]['d120']        = round($d120_all / $count) . "%";
            $xlsData[$count]['d180']        = round($d180_all / $count) . "%";
            $xlsData[$count]['d360']        = round($d360_all / $count) . "%";
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * @Notes:设备留存
     * @Interface memberRemainExport
     * @param $map
     */
    public function facilityRemainExport($map)
    {
        $xlsName = "设备留存";
        $xlsCell = array(
            0  => array('day_time', '日期'),
            1  => array('device_num', '新增设备数'),
            2  => array('d2', '2日留存'),
            3  => array('d3', '3日留存'),
            4  => array('d4', '4日留存'),
            5  => array('d5', '5日留存'),
            6  => array('d6', '6日留存'),
            7  => array('d7', '7日留存'),
            8  => array('d14', '14日留存'),
            9  => array('d30', '30日留存'),
            10 => array('d45', '45日留存'),
            11 => array('d60', '60日留存'),
            12 => array('d90', '90日留存'),
            13 => array('d120', '120日留存'),
            14 => array('d180', '180日留存'),
            15 => array('d360', '360日留存'),
        );

        $list = (new BoxFacilityRemainLog())
            ->field('day_time, sum(device_num) as device_num, sum(d2) as d2, sum(d3) as d3, sum(d4) as d4, sum(d5) as d5, sum(d6) as d6, sum(d7) as d7, sum(d14) as d14, sum(d30) as d30, sum(d45) as d45, sum(d60) as d60, sum(d90) as d90, sum(d120) as d120, sum(d180) as d180, sum(d360) as d360')
            ->where($map)
            ->order('day_time', 'desc');

        if (empty($param['platform'])) {
            $list->group('day_time');
        } else {
            $list->group('day_time,platform');
        }

        $xlsData = $list->select()->toArray();
        //获取数据总数
        $count = count($xlsData);

        if (!empty($xlsData)) {
            $device_num_all = 0;
            $d2_all         = 0;
            $d3_all         = 0;
            $d4_all         = 0;
            $d5_all         = 0;
            $d6_all         = 0;
            $d7_all         = 0;
            $d14_all        = 0;
            $d30_all        = 0;
            $d45_all        = 0;
            $d60_all        = 0;
            $d90_all        = 0;
            $d120_all       = 0;
            $d180_all       = 0;
            $d360_all       = 0;
            foreach ($xlsData as $key => &$item) {
                $device_num_all += $item['device_num'];
                if (!empty($item['device_num'])) {
                    $d2_all   += round($item['d2'] / $item['device_num'] * 100);
                    $d3_all   += round($item['d3'] / $item['device_num'] * 100);
                    $d4_all   += round($item['d4'] / $item['device_num'] * 100);
                    $d5_all   += round($item['d5'] / $item['device_num'] * 100);
                    $d6_all   += round($item['d6'] / $item['device_num'] * 100);
                    $d7_all   += round($item['d7'] / $item['device_num'] * 100);
                    $d14_all  += round($item['d14'] / $item['device_num'] * 100);
                    $d30_all  += round($item['d30'] / $item['device_num'] * 100);
                    $d45_all  += round($item['d45'] / $item['device_num'] * 100);
                    $d60_all  += round($item['d60'] / $item['device_num'] * 100);
                    $d90_all  += round($item['d90'] / $item['device_num'] * 100);
                    $d120_all += round($item['d120'] / $item['device_num'] * 100);
                    $d180_all += round($item['d180'] / $item['device_num'] * 100);
                    $d360_all += round($item['d360'] / $item['device_num'] * 100);
                }

                $item['day_time'] = date("Y-m-d", strtotime($item['day_time']));//日期
                $item['d2']       = empty($item['device_num']) ? "0%" : round($item['d2'] / $item['device_num'] * 100) . "%";//2日留存
                $item['d3']       = empty($item['device_num']) ? "0%" : round($item['d3'] / $item['device_num'] * 100) . "%";//2日留存
                $item['d4']       = empty($item['device_num']) ? "0%" : round($item['d4'] / $item['device_num'] * 100) . "%";//2日留存
                $item['d5']       = empty($item['device_num']) ? "0%" : round($item['d5'] / $item['device_num'] * 100) . "%";//2日留存
                $item['d6']       = empty($item['device_num']) ? "0%" : round($item['d6'] / $item['device_num'] * 100) . "%";//2日留存
                $item['d7']       = empty($item['device_num']) ? "0%" : round($item['d7'] / $item['device_num'] * 100) . "%";//2日留存
                $item['d14']      = empty($item['device_num']) ? "0%" : round($item['d14'] / $item['device_num'] * 100) . "%";//2日留存
                $item['d30']      = empty($item['device_num']) ? "0%" : round($item['d30'] / $item['device_num'] * 100) . "%";//2日留存
                $item['d45']      = empty($item['device_num']) ? "0%" : round($item['d45'] / $item['device_num'] * 100) . "%";//2日留存
                $item['d60']      = empty($item['device_num']) ? "0%" : round($item['d60'] / $item['device_num'] * 100) . "%";//2日留存
                $item['d90']      = empty($item['device_num']) ? "0%" : round($item['d90'] / $item['device_num'] * 100) . "%";//2日留存
                $item['d120']     = empty($item['device_num']) ? "0%" : round($item['d120'] / $item['device_num'] * 100) . "%";//2日留存
                $item['d180']     = empty($item['device_num']) ? "0%" : round($item['d180'] / $item['device_num'] * 100) . "%";//2日留存
                $item['d360']     = empty($item['device_num']) ? "0%" : round($item['d360'] / $item['device_num'] * 100) . "%";//2日留存

            }
            $xlsData[$count]['day_time']   = "汇总";
            $xlsData[$count]['device_num'] = $device_num_all;
            $xlsData[$count]['d2']         = round($d2_all / $count) . "%";
            $xlsData[$count]['d3']         = round($d3_all / $count) . "%";
            $xlsData[$count]['d4']         = round($d4_all / $count) . "%";
            $xlsData[$count]['d5']         = round($d5_all / $count) . "%";
            $xlsData[$count]['d6']         = round($d6_all / $count) . "%";
            $xlsData[$count]['d7']         = round($d7_all / $count) . "%";
            $xlsData[$count]['d14']        = round($d14_all / $count) . "%";
            $xlsData[$count]['d30']        = round($d30_all / $count) . "%";
            $xlsData[$count]['d45']        = round($d45_all / $count) . "%";
            $xlsData[$count]['d60']        = round($d60_all / $count) . "%";
            $xlsData[$count]['d90']        = round($d90_all / $count) . "%";
            $xlsData[$count]['d120']       = round($d120_all / $count) . "%";
            $xlsData[$count]['d180']       = round($d180_all / $count) . "%";
            $xlsData[$count]['d360']       = round($d360_all / $count) . "%";
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * @Notes:导出广告投放统计
     * @Interface adOverviewExport
     * @param $map
     */
    public function adOverviewExport($map)
    {
        $xlsName = "广告投放统计";
        $xlsCell = array(
            0  => array('day_time', '日期'),
            1  => array('media_name', '投放渠道'),
            2  => array('package_name', '投放产品名称'),
            3  => array('platform', '产品类型'),
            4  => array('promote_name', '广告位名称'),
            5  => array('pv', '点击量（PV）'),
            6  => array('active_num', '激活数'),
            7  => array('active_rate', '激活转化率'),
            8  => array('register_num', '注册数'),
            9  => array('pay_people_num', '付费人数'),
            10 => array('pay_amount', '付费金额'),
            11 => array('roi', '首日roi'),
            12 => array('all_pay_account_num', '累计付费人数'),
            13 => array('all_pay_amount', '累计用户付费金额'),
            14 => array('all_roi', '累计roi'),
            15 => array('cost', '成本'),
        );

        $xlsData = (new BoxStatisticsAdOverviewLog())
            ->field('day_time,platform,package_id,media_id,promote_id,pv,active_num,register_num,pay_people_num,pay_amount,cost,all_pay_account_num,all_pay_amount')
            ->where($map)
            ->order('day_time', 'desc')
            ->select()->toArray();
        //获取数据总数
        $count = count($xlsData);

        //媒体
        $promoteData = UserPromote::field(['id', 'realname'])->select()->toArray();
        $promoteData = array_column($promoteData, 'realname', 'id');

        //产品
        $packageData            = Package::field(['id', 'package_name'])->select()->toArray();
        $packageData            = array_column($packageData, 'package_name', 'id');
        $packageData['9999997'] = '千年游戏盒子pc';
        $packageData['9999998'] = '千年游戏盒子ios';
        $packageData['9999999'] = '千年游戏盒子android';

        //投放渠道
        $mediaData = BoxAdMedia::field(['id', 'name'])->select()->toArray();
        $mediaData = array_column($mediaData, 'name', 'id');

        if (!empty($xlsData)) {
            $pv_all              = 0;
            $active_num_all      = 0;
            $active_rate_all     = 0;
            $register_num_all    = 0;
            $pay_people_num_all  = 0;
            $pay_amount_all      = 0;
            $roi_all             = 0;
            $all_pay_account_num = 0;
            $all_pay_amount      = 0;
            $all_roi             = 0;
            $cost_all            = 0;
            foreach ($xlsData as $key => &$item) {
                $item['media_name']   = $mediaData[$item['media_id']] ?? '';
                $item['promote_name'] = $promoteData[$item['promote_id']] ?? '';
                $item['package_name'] = $packageData[$item['package_id']] ?? '';
                $item['active_rate']  = empty($item['pv']) ? '0%' : round($item['active_num'] / $item['pv'] * 100, 2) . "%";
                if (empty($item['cost']) || $item['cost'] == '0.00') {
                    $item['roi']     = 0;
                    $item['all_roi'] = 0;
                    $roi             = 0;
                    $all_roi_all     = 0;
                } else {
                    $item['roi']     = round($item['pay_amount'] / $item['cost'] * 100, 2) . "%";
                    $item['all_roi'] = round($item['all_pay_amount'] / $item['cost'] * 100, 2) . "%";
                    $roi             = round($item['pay_amount'] / $item['cost'] * 100, 2);
                    $all_roi_all     = round($item['all_pay_amount'] / $item['cost'] * 100, 2);
                }
                $item['day_time'] = date("Y-m-d", strtotime($item['day_time']));
                $item['platform'] = $item['platform'] == 1 ? 'ios' : $item['platform'] == 2 ? 'android' : '其他';

                $pv_all              += $item['pv'];
                $active_num_all      += $item['active_num'];
                $active_rate_all     += empty($item['pv']) ? '0' : round($item['active_num'] / $item['pv'] * 100, 2);
                $register_num_all    += $item['register_num'];
                $pay_people_num_all  += $item['pay_people_num'];
                $pay_amount_all      += $item['pay_amount'];
                $roi_all             += $roi;
                $all_pay_account_num += $item['all_pay_account_num'];
                $all_pay_amount      += $item['all_pay_amount'];
                $all_roi             += $all_roi_all;
                $cost_all            += $item['cost'];
            }

            $xlsData[$count]['day_time']            = "汇总";
            $xlsData[$count]['media_name']          = "-";
            $xlsData[$count]['package_name']        = "-";
            $xlsData[$count]['platform']            = "-";
            $xlsData[$count]['promote_name']        = "-";
            $xlsData[$count]['pv']                  = $pv_all;
            $xlsData[$count]['active_num']          = $active_num_all;
            $xlsData[$count]['active_rate']         = empty($pv_all) ? '0%' : round($active_num_all / $pv_all / $count * 100, 2) . "%";
            $xlsData[$count]['register_num']        = $register_num_all;
            $xlsData[$count]['pay_people_num']      = $pay_people_num_all;
            $xlsData[$count]['pay_amount']          = $pay_amount_all;
            $xlsData[$count]['roi']                 = empty($cost_all) ? '0%' : round($pay_amount_all / $cost_all * 100, 2) . "%";
            $xlsData[$count]['all_pay_account_num'] = $all_pay_account_num;
            $xlsData[$count]['all_pay_amount']      = $all_pay_amount;
            $xlsData[$count]['all_roi']             = empty($cost_all) ? '0%' : round($all_pay_amount / $cost_all * 100, 2) . "%";
            $xlsData[$count]['cost']                = $cost_all;
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * @Notes:下载广告投放统计模板
     * @Interface adOverviewDownloadModel
     * @param $map
     */
    public function adOverviewDownloadModel($map)
    {
        $xlsName = "广告投放成本导入模板";
        $xlsCell = array(
            0 => array('day_time', '日期'),
            1 => array('media_id', '投放编号'),
            2 => array('media_name', '投放渠道'),
            3 => array('package_id', '投放产品编号'),
            4 => array('package_name', '投放产品名称'),
            5 => array('platform', '产品类型编号'),
            6 => array('platform_name', '产品类型'),
            7 => array('promote_id', '广告位编号'),
            8 => array('promote_name', '广告位名称'),
            9 => array('cost', '成本'),
        );

        $xlsData = (new BoxStatisticsAdOverviewLog())
            ->field('day_time,platform,package_id,media_id,promote_id,cost')
            ->where($map)
            ->order('day_time', 'desc')
            ->select()->toArray();

        //媒体
        $promoteData = UserPromote::field(['id', 'realname'])->select()->toArray();
        $promoteData = array_column($promoteData, 'realname', 'id');

        //产品
        $packageData            = Package::field(['id', 'package_name'])->select()->toArray();
        $packageData            = array_column($packageData, 'package_name', 'id');
        $packageData['9999998'] = '千年游戏盒子ios';
        $packageData['9999999'] = '千年游戏盒子android';

        //投放渠道
        $mediaData = BoxAdMedia::field(['id', 'name'])->select()->toArray();
        $mediaData = array_column($mediaData, 'name', 'id');

        if (!empty($xlsData)) {
            foreach ($xlsData as $key => &$item) {
                $item['media_name']    = $mediaData[$item['media_id']] ?? '';
                $item['promote_name']  = $promoteData[$item['promote_id']] ?? '';
                $item['package_name']  = $packageData[$item['package_id']] ?? '';
                $item['platform_name'] = $item['platform'] == 1 ? 'ios' : $item['platform'] == 2 ? 'android' : '其他';
                $item['day_time']      = date("Y-m-d", strtotime($item['day_time']));
            }
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * @Notes:导出广告投放留存
     * @Interface adRemainExport
     * @param $map
     */
    public function adRemainExport($map)
    {
        $xlsName = "广告投放留存";
        $xlsCell = array(
            0  => array('day_time', '日期'),
            1  => array('media_name', '投放渠道'),
            2  => array('package_name', '投放产品名称'),
            3  => array('platform', '产品类型'),
            4  => array('promote_name', '广告位名称'),
            5  => array('account_num', '注册数'),
            6  => array('d2', '2日留存'),
            7  => array('d3', '3日留存'),
            8  => array('d4', '4日留存'),
            9  => array('d5', '5日留存'),
            10 => array('d6', '6日留存'),
            11 => array('d7', '7日留存'),
            12 => array('d14', '14日留存'),
            13 => array('d30', '30日留存'),
            14 => array('d60', '60日留存'),
            15 => array('d90', '90日留存'),
            16 => array('d180', '180日留存'),
        );

        $xlsData = (new BoxStatisticsAdRemainLog())
            ->field('day_time,platform,promote_id,media_id,package_id,account_num,d2,d3,d4,d5,d6,d7,d14,d30,d60,d90,d180')
            ->where($map)
            ->order('day_time', 'desc')
            ->select()->toArray();
        //获取数据总数
        $count = count($xlsData);

        //媒体
        $promoteData = UserPromote::field(['id', 'realname'])->select()->toArray();
        $promoteData = array_column($promoteData, 'realname', 'id');

        //产品
        $packageData            = Package::field(['id', 'package_name'])->select()->toArray();
        $packageData            = array_column($packageData, 'package_name', 'id');
        $packageData['9999997'] = '千年游戏盒子pc';
        $packageData['9999998'] = '千年游戏盒子ios';
        $packageData['9999999'] = '千年游戏盒子android';

        //投放渠道
        $mediaData = BoxAdMedia::field(['id', 'name'])->select()->toArray();
        $mediaData = array_column($mediaData, 'name', 'id');

        if (!empty($xlsData)) {
            $account_num_all = 0;
            $d2_all          = 0;
            $d3_all          = 0;
            $d4_all          = 0;
            $d5_all          = 0;
            $d6_all          = 0;
            $d7_all          = 0;
            $d14_all         = 0;
            $d30_all         = 0;
            $d60_all         = 0;
            $d90_all         = 0;
            $d180_all        = 0;
            foreach ($xlsData as $key => &$item) {
                $account_num_all += $item['account_num'];
                if (!empty($item['account_num'])) {
                    $d2_all   += round($item['d2'] / $item['account_num'] * 100);
                    $d3_all   += round($item['d3'] / $item['account_num'] * 100);
                    $d4_all   += round($item['d4'] / $item['account_num'] * 100);
                    $d5_all   += round($item['d5'] / $item['account_num'] * 100);
                    $d6_all   += round($item['d6'] / $item['account_num'] * 100);
                    $d7_all   += round($item['d7'] / $item['account_num'] * 100);
                    $d14_all  += round($item['d14'] / $item['account_num'] * 100);
                    $d30_all  += round($item['d30'] / $item['account_num'] * 100);
                    $d60_all  += round($item['d60'] / $item['account_num'] * 100);
                    $d90_all  += round($item['d90'] / $item['account_num'] * 100);
                    $d180_all += round($item['d180'] / $item['account_num'] * 100);
                }

                $item['media_name']   = $mediaData[$item['media_id']] ?? '';
                $item['promote_name'] = $promoteData[$item['promote_id']] ?? '';
                $item['package_name'] = $packageData[$item['package_id']] ?? '';
                $item['day_time']     = date("Y-m-d", strtotime($item['day_time']));
                $item['d2']           = empty($item['account_num']) ? '0%' : round($item['d2'] / $item['account_num'] * 100) . "%";
                $item['d3']           = empty($item['account_num']) ? '0%' : round($item['d3'] / $item['account_num'] * 100) . "%";
                $item['d4']           = empty($item['account_num']) ? '0%' : round($item['d4'] / $item['account_num'] * 100) . "%";
                $item['d5']           = empty($item['account_num']) ? '0%' : round($item['d5'] / $item['account_num'] * 100) . "%";
                $item['d6']           = empty($item['account_num']) ? '0%' : round($item['d6'] / $item['account_num'] * 100) . "%";
                $item['d7']           = empty($item['account_num']) ? '0%' : round($item['d7'] / $item['account_num'] * 100) . "%";
                $item['d14']          = empty($item['account_num']) ? '0%' : round($item['d14'] / $item['account_num'] * 100) . "%";
                $item['d30']          = empty($item['account_num']) ? '0%' : round($item['d30'] / $item['account_num'] * 100) . "%";
                $item['d60']          = empty($item['account_num']) ? '0%' : round($item['d60'] / $item['account_num'] * 100) . "%";
                $item['d90']          = empty($item['account_num']) ? '0%' : round($item['d90'] / $item['account_num'] * 100) . "%";
                $item['d180']         = empty($item['account_num']) ? '0%' : round($item['d180'] / $item['account_num'] * 100) . "%";
            }
            $xlsData[$count]['day_time']     = "汇总";
            $xlsData[$count]['media_name']   = "-";
            $xlsData[$count]['package_name'] = "-";
            $xlsData[$count]['platform']     = "-";
            $xlsData[$count]['promote_name'] = "-";
            $xlsData[$count]['account_num']  = $account_num_all;
            $xlsData[$count]['d2']           = round($d2_all / $count) . "%";
            $xlsData[$count]['d3']           = round($d3_all / $count) . "%";
            $xlsData[$count]['d4']           = round($d4_all / $count) . "%";
            $xlsData[$count]['d5']           = round($d5_all / $count) . "%";
            $xlsData[$count]['d6']           = round($d6_all / $count) . "%";
            $xlsData[$count]['d7']           = round($d7_all / $count) . "%";
            $xlsData[$count]['d14']          = round($d14_all / $count) . "%";
            $xlsData[$count]['d30']          = round($d30_all / $count) . "%";
            $xlsData[$count]['d60']          = round($d60_all / $count) . "%";
            $xlsData[$count]['d90']          = round($d90_all / $count) . "%";
            $xlsData[$count]['d180']         = round($d180_all / $count) . "%";
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * @Notes:导出广告投放LTV
     * @Interface adLtvExport
     * @param $map
     */
    public function adLtvExport($map)
    {
        $xlsName = "广告投放LTV";
        $xlsCell = array(
            0  => array('day_time', '日期'),
            1  => array('media_name', '投放渠道'),
            2  => array('package_name', '产品名称'),
            3  => array('platform', '产品类型'),
            4  => array('promote_name', '广告位名称'),
            5  => array('account_num', '新增注册数'),
            6  => array('ltv_total', '汇总LTV'),
            7  => array('d1', 'D1LTV'),
            8  => array('d2', 'D2LTV'),
            9  => array('d3', 'D3LTV'),
            10 => array('d4', 'D4LTV'),
            11 => array('d5', 'D5LTV'),
            12 => array('d6', 'D6LTV'),
            13 => array('d7', 'D7LTV'),
            14 => array('d14', 'D14LTV'),
            15 => array('d30', 'D30LTV'),
            16 => array('d45', 'D45LTV'),
            17 => array('d60', 'D60LTV'),
            18 => array('d90', 'D90LTV'),
            19 => array('d120', 'D120LTV'),
            20 => array('d180', 'D180LTV'),
            21 => array('d360', 'D360LTV'),
        );

        $xlsData = (new BoxStatisticsAdLtvLog())
            ->field('day_time,platform,promote_id,media_id,package_id,account_num,d1,d2,d3,d4,d5,d6,d7,d14,d30,d45,d60,d90,d120,d180,d360,dnow')
            ->where($map)
            ->order('day_time', 'desc')
            ->select()->toArray();
        //获取数据总数
        $count = count($xlsData);

        //媒体
        $promoteData = UserPromote::field(['id', 'realname'])->select()->toArray();
        $promoteData = array_column($promoteData, 'realname', 'id');

        //产品
        $packageData            = Package::field(['id', 'package_name'])->select()->toArray();
        $packageData            = array_column($packageData, 'package_name', 'id');
        $packageData['9999997'] = '千年游戏盒子pc';
        $packageData['9999998'] = '千年游戏盒子ios';
        $packageData['9999999'] = '千年游戏盒子android';

        //投放渠道
        $mediaData = BoxAdMedia::field(['id', 'name'])->select()->toArray();
        $mediaData = array_column($mediaData, 'name', 'id');


        if (!empty($xlsData)) {
            $account_num_all = 0;
            $ltv_total_all   = 0;
            $d1_all          = 0;
            $d2_all          = 0;
            $d3_all          = 0;
            $d4_all          = 0;
            $d5_all          = 0;
            $d6_all          = 0;
            $d7_all          = 0;
            $d14_all         = 0;
            $d30_all         = 0;
            $d45_all         = 0;
            $d60_all         = 0;
            $d90_all         = 0;
            $d120_all        = 0;
            $d180_all        = 0;
            $d360_all        = 0;
            foreach ($xlsData as $key => &$item) {
                $day     = diffTimeRetrunDay(strtotime($item['day_time']), strtotime(date("Y-m-d")));
                $day     = $day + 1;
                $dayList = ['1', '2', '3', '4', '5', '6', '7', '14', '30', '45', '60', '90', '120', '180', '360'];
                $dayNum  = findValue($day, $dayList);
                $dayNum  = "d" . $dayNum;

                $item['media_name']   = $mediaData[$item['media_id']] ?? '';
                $item['promote_name'] = $promoteData[$item['promote_id']] ?? '';
                $item['package_name'] = $packageData[$item['package_id']] ?? '';
                $item['day_time']     = date("Y-m-d", strtotime($item['day_time']));
                $item['ltv_total']    = round($item[$dayNum] / $item['account_num'], 2);

                $num = findMinValue($day, $dayList);
                foreach ($dayList as $dayItem) {
                    $dayItemNum = "d" . $dayItem;
                    if ($dayItem <= $num) {
                        $item[$dayItemNum] = empty($item['account_num']) ? "0" : round($item[$dayItemNum] / $item['account_num'], 2);
                    } else {
                        $item[$dayItemNum] = '0';
                    }
                }
                $account_num_all += $item['account_num'];
                $ltv_total_all   += $item[$dayNum];
                $d1_all          += $item['d1'];
                $d2_all          += $item['d2'];
                $d3_all          += $item['d3'];
                $d4_all          += $item['d4'];
                $d5_all          += $item['d5'];
                $d6_all          += $item['d6'];
                $d7_all          += $item['d7'];
                $d14_all         += $item['d14'];
                $d30_all         += $item['d30'];
                $d45_all         += $item['d45'];
                $d60_all         += $item['d60'];
                $d90_all         += $item['d90'];
                $d120_all        += $item['d120'];
                $d180_all        += $item['d180'];
                $d360_all        += $item['d360'];
            }
            $xlsData[$count]['day_time']     = "汇总";
            $xlsData[$count]['media_name']   = "-";
            $xlsData[$count]['package_name'] = "-";
            $xlsData[$count]['platform']     = "-";
            $xlsData[$count]['promote_name'] = "-";
            $xlsData[$count]['account_num']  = $account_num_all;
            $xlsData[$count]['ltv_total']    = round($ltv_total_all / $count, 2) ?? '0';
            $xlsData[$count]['d1']           = round($d1_all / $count, 2) ?? '0';
            $xlsData[$count]['d2']           = round($d2_all / $count, 2) ?? '0';
            $xlsData[$count]['d3']           = round($d3_all / $count, 2) ?? '0';
            $xlsData[$count]['d4']           = round($d4_all / $count, 2) ?? '0';
            $xlsData[$count]['d5']           = round($d5_all / $count, 2) ?? '0';
            $xlsData[$count]['d6']           = round($d6_all / $count, 2) ?? '0';
            $xlsData[$count]['d7']           = round($d7_all / $count, 2) ?? '0';
            $xlsData[$count]['d14']          = round($d14_all / $count, 2) ?? '0';
            $xlsData[$count]['d30']          = round($d30_all / $count, 2) ?? '0';
            $xlsData[$count]['d45']          = round($d45_all / $count, 2) ?? '0';
            $xlsData[$count]['d60']          = round($d60_all / $count, 2) ?? '0';
            $xlsData[$count]['d90']          = round($d90_all / $count, 2) ?? '0';
            $xlsData[$count]['d120']         = round($d120_all / $count, 2) ?? '0';
            $xlsData[$count]['d180']         = round($d180_all / $count, 2) ?? '0';
            $xlsData[$count]['d360']         = round($d360_all / $count, 2) ?? '0';
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * @Notes:导出盒子投放效果追踪
     * @Interface adTrackExport
     * @param $map
     */
    public function adTrackExport($map)
    {
        $xlsName = "盒子投放效果追踪";
        $xlsCell = array(
            0  => array('day_time', '日期'),
            1  => array('media_name', '投放渠道'),
            2  => array('package_name', '产品名称'),
            3  => array('platform', '产品类型'),
            4  => array('promote_name', '广告位名称'),
            5  => array('box_register_num', '新增注册数'),
            6  => array('box_game_num', '当日游戏注册的主账号数'),
            7  => array('game_register_rate', '当日游戏注册转化率'),
            8  => array('box_role_num', '当日游戏创角的主账号数'),
            9  => array('game_total_register_num', '当日游戏创角转化率'),
            10 => array('game_register_num', '当日游戏注册小号数'),
            11 => array('role_num', '当日游戏创角数'),
            12 => array('cost', '当日投放成本'),
        );
        $xlsData = (new BoxStatisticsAdTrackLog())
            ->field('day_time,platform,promote_id,package_id,media_id,box_register_num,box_game_num,box_role_num,game_register_num,role_num,cost')
            ->where($map)
            ->order('day_time', 'desc')
            ->select()->toArray();
        //获取数据总数
        $count = count($xlsData);

        //媒体
        $promoteData = UserPromote::field(['id', 'realname'])->select()->toArray();
        $promoteData = array_column($promoteData, 'realname', 'id');

        //产品
        $packageData            = Package::field(['id', 'package_name'])->select()->toArray();
        $packageData            = array_column($packageData, 'package_name', 'id');
        $packageData['9999997'] = '千年游戏盒子pc';
        $packageData['9999998'] = '千年游戏盒子ios';
        $packageData['9999999'] = '千年游戏盒子android';

        //投放渠道
        $mediaData = BoxAdMedia::field(['id', 'name'])->select()->toArray();
        $mediaData = array_column($mediaData, 'name', 'id');

        if (!empty($xlsData)) {
            $box_register_num_all        = 0;
            $box_game_num_all            = 0;
            $game_register_rate_all      = 0;
            $box_role_num_all            = 0;
            $game_total_register_num_all = 0;
            $game_register_num_all       = 0;
            $role_num_all                = 0;
            $cost_all                    = 0;
            foreach ($xlsData as $key => &$item) {
                $box_register_num_all        += $item['box_register_num'];
                $box_game_num_all            += $item['box_game_num'];
                $game_register_rate_all      += empty($item['box_register_num']) ? '0' : round($item['box_game_num'] / $item['box_register_num'] * 100, 2) ?? '0';
                $box_role_num_all            += $item['box_role_num'];
                $game_total_register_num_all += empty($item['box_register_num']) ? '0' : round($item['box_role_num'] / $item['box_register_num'] * 100, 2) ?? '0';
                $game_register_num_all       += $item['game_register_num'];
                $role_num_all                += $item['role_num'];
                $cost_all                    += $item['cost'];

                $item['day_time']                = date("Y-m-d", strtotime($item['day_time']));
                $item['media_name']              = $mediaData[$item['media_id']] ?? '';
                $item['promote_name']            = $promoteData[$item['promote_id']] ?? '';
                $item['package_name']            = $packageData[$item['package_id']] ?? '';
                $item['game_register_rate']      = empty($item['box_register_num']) ? '0%' : round($item['box_game_num'] / $item['box_register_num'] * 100, 2) . "%";
                $item['game_total_register_num'] = empty($item['box_register_num']) ? '0%' : round($item['box_role_num'] / $item['box_register_num'] * 100, 2) . "%";

            }
            $xlsData[$count]['day_time']                = "汇总";
            $xlsData[$count]['media_name']              = "-";
            $xlsData[$count]['package_name']            = "-";
            $xlsData[$count]['platform']                = "-";
            $xlsData[$count]['promote_name']            = "-";
            $xlsData[$count]['box_register_num']        = $box_register_num_all;
            $xlsData[$count]['box_game_num']            = $box_game_num_all;
            $xlsData[$count]['game_register_rate']      = round($game_register_rate_all / $count, 2) . "%";
            $xlsData[$count]['box_role_num']            = $box_role_num_all;
            $xlsData[$count]['game_total_register_num'] = round($game_total_register_num_all / $count, 2) . "%";
            $xlsData[$count]['game_register_num']       = $game_register_num_all;
            $xlsData[$count]['role_num']                = $role_num_all;
            $xlsData[$count]['cost']                    = $cost_all;
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }


    /**
     * @Notes:导出盒子投放游戏数据总览
     * @Interface adTrackExport
     * @param $map
     */
    public function adGameTrackExport($param, $map)
    {
        $xlsName = "盒子投放游戏数据总览";
        $xlsCell = array(
            0  => array('day_time', '日期'),
            1  => array('media_name', '投放渠道'),
            2  => array('package_name', '产品名称'),
            3  => array('platform', '产品类型'),
            4  => array('promote_name', '广告位名称'),
            5  => array('game_name', '游戏名称'),
            6  => array('runing_water', '流水'),
            7  => array('register_num', '新增账号数'),
            8  => array('new_register_num', '新增创角账号数'),
            9  => array('role_rate', '创角率'),
            10 => array('new_role_num', '新增角色数'),
            11 => array('active_account_num', '活跃账号数'),
            12 => array('pay_num', '付费账号数'),
            13 => array('pay_rate', '账号付费率'),
            14 => array('arppu', 'ARPPU'),
            15 => array('darpu', 'Darpu'),
            16 => array('new_pay_num', '新增账号付费数'),
            17 => array('new_pay_amount', '新增账号付费金额'),
            18 => array('new_pay_rate', '新增账号付费率'),
            19 => array('new_arppu', '新增账号ARPPU'),
        );

        $list = (new BoxStatisticsAdGameTrackLog())
            ->field('day_time,platform,promote_id,box_game_id,package_id,media_id,sum(runing_water) as runing_water,sum(register_num) as register_num,sum(new_register_num) as new_register_num,sum(new_role_num) as new_role_num,sum(active_account_num) as active_account_num,sum(pay_num) as pay_num,sum(new_pay_num) as new_pay_num,sum(new_pay_amount) as new_pay_amount')
            ->where($map)
            ->order('day_time', 'desc');

        if (empty($param['game_id'])) {
            $list->group('day_time');
        } else {
            $list->group('day_time,box_game_id');
        }

        $xlsData = $list->select()->toArray();
        //获取数据总数
        $count = count($xlsData);

        //游戏
        $gameData = BoxGame::field(['id', 'game_name'])->select()->toArray();
        $gameData = array_column($gameData, 'game_name', 'id');

        //媒体
        $promoteData = UserPromote::field(['id', 'realname'])->select()->toArray();
        $promoteData = array_column($promoteData, 'realname', 'id');

        //产品
        $packageData            = Package::field(['id', 'package_name'])->select()->toArray();
        $packageData            = array_column($packageData, 'package_name', 'id');
        $packageData['9999997'] = '千年游戏盒子pc';
        $packageData['9999998'] = '千年游戏盒子ios';
        $packageData['9999999'] = '千年游戏盒子android';

        //投放渠道
        $mediaData = BoxAdMedia::field(['id', 'name'])->select()->toArray();
        $mediaData = array_column($mediaData, 'name', 'id');

        if (!empty($xlsData)) {
            $runing_water_all       = 0;
            $register_num_all       = 0;
            $new_register_num_all   = 0;
            $role_rate_all          = 0;
            $new_role_num_all       = 0;
            $active_account_num_all = 0;
            $pay_num_all            = 0;
            $pay_rate_all           = 0;
            $arppu_all              = 0;
            $darpu_all              = 0;
            $new_pay_num_all        = 0;
            $new_pay_amount_all     = 0;
            $new_pay_rate_all       = 0;
            $new_arppu_all          = 0;
            foreach ($xlsData as $key => &$item) {
                $item['day_time']     = date("Y-m-d", strtotime($item['day_time']));
                $item['media_name']   = empty($param['media_id']) ? '-' : $mediaData[$item['media_id']] ?? '';
                $item['promote_name'] = empty($param['promote_id']) ? '-' : $promoteData[$item['promote_id']] ?? '';
                $item['platform']     = empty($param['platform']) ? '-' : $item['platform'];
                $item['package_name'] = empty($param['platform']) ? '-' : $packageData[$item['package_id']] ?? '';
                $item['game_name']    = empty($param['game_id']) ? '全部游戏' : $gameData[$item['box_game_id']] ?? '';
                $item['role_rate']    = empty($item['register_num']) ? '0' : round($item['new_register_num'] / $item['register_num'] * 100);
                $item['pay_rate']     = empty($item['active_account_num']) ? '0' : round($item['pay_num'] / $item['active_account_num'] * 100);
                $item['arppu']        = empty($item['pay_num']) ? '0' : round($item['new_pay_amount'] / $item['pay_num'], 2);
                $item['darpu']        = empty($item['active_account_num']) ? '0' : round($item['new_pay_amount'] / $item['active_account_num'], 2);
                $item['new_pay_rate'] = empty($item['register_num']) ? '0' : round($item['new_pay_num'] / $item['register_num'] * 100);
                $item['new_arppu']    = empty($item['new_pay_num']) ? '0' : round($item['new_pay_amount'] / $item['new_pay_num'], 2);

                $runing_water_all       += $item['runing_water'];
                $register_num_all       += $item['register_num'];
                $new_register_num_all   += $item['new_register_num'];
                $role_rate_all          += $item['role_rate'];
                $new_role_num_all       += $item['new_role_num'];
                $active_account_num_all += $item['active_account_num'];
                $pay_num_all            += $item['pay_num'];
                $pay_rate_all           += $item['pay_rate'];
                $arppu_all              += $item['arppu'];
                $darpu_all              += $item['darpu'];
                $new_pay_num_all        += $item['new_pay_num'];
                $new_pay_amount_all     += $item['new_pay_amount'];
                $new_pay_rate_all       += $item['new_pay_rate'];
                $new_arppu_all          += $item['new_arppu'];

                $item['role_rate']    = $item['role_rate'] . "%";
                $item['pay_rate']     = $item['pay_rate'] . "%";
                $item['new_pay_rate'] = $item['new_pay_rate'] . "%";
            }

            $xlsData[$count]['day_time']           = "汇总";
            $xlsData[$count]['media_name']         = "-";
            $xlsData[$count]['package_name']       = "-";
            $xlsData[$count]['platform']           = "-";
            $xlsData[$count]['promote_name']       = "-";
            $xlsData[$count]['game_name']          = "-";
            $xlsData[$count]['runing_water']       = $runing_water_all;
            $xlsData[$count]['register_num']       = $register_num_all;
            $xlsData[$count]['new_register_num']   = $new_register_num_all;
            $xlsData[$count]['role_rate']          = round($role_rate_all / $count) . "%";
            $xlsData[$count]['new_role_num']       = $new_role_num_all;
            $xlsData[$count]['active_account_num'] = $active_account_num_all;
            $xlsData[$count]['pay_num']            = $pay_num_all;
            $xlsData[$count]['pay_rate']           = round($pay_rate_all / $count, 2) . "%";
            $xlsData[$count]['arppu']              = round($arppu_all / $count, 2);
            $xlsData[$count]['darpu']              = round($darpu_all / $count, 2);
            $xlsData[$count]['new_pay_num']        = $new_pay_num_all;
            $xlsData[$count]['new_pay_amount']     = $new_pay_amount_all;
            $xlsData[$count]['new_pay_rate']       = round($new_pay_rate_all / $count, 2) . "%";
            $xlsData[$count]['new_arppu']          = round($new_arppu_all / $count, 2);
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }


    /**
     * @Notes:导出数据总览
     * @Interface gameOverviewExport
     * @param $map
     */
    public function gameOverviewExport($param, $map)
    {
        $xlsName = "数据总览";
        $xlsCell = array(
            0  => array('day_time', '日期'),
            1  => array('pay_amount', '流水（付费金额）'),
            2  => array('game_name', '游戏名称'),
            3  => array('account_num', '新增账号数'),
            4  => array('role_num', '新增创角账号数'),
            5  => array('role_rate', '创角率'),
            6  => array('role_num_all', '新增角色数量'),
            7  => array('active_account_num', '活跃账号数量'),
            8  => array('pay_account_num', '付费账号数量'),
            9  => array('active_account_rate', '账号付费率'),
            10 => array('arppu', 'ARPPU'),
            11 => array('dappu', 'Darpu'),
            12 => array('new_account_num', '新增账号付费数'),
            13 => array('new_account_amount', '新增账号付费金额'),
            14 => array('new_account_rate', '新增账号付费率'),
            15 => array('new_account_arppu', '新增账号ARPPU'),
        );

        $list = (new BoxGameLog())
            ->field('day_time,sum(pay_amount) as pay_amount,box_game_id,sum(account_num) as account_num,sum(role_num) as role_num,sum(role_num_all) as role_num_all,sum(active_account_num) as active_account_num,sum(pay_account_num) as pay_account_num,sum(new_account_num) as new_account_num,sum(new_account_amount) as new_account_amount')
            ->where($map)
            ->order('day_time', 'desc');

        if (empty($param['box_game_id'])) {
            $list->group('day_time');
        } else {
            $list->group('day_time,box_game_id');
        }

        $list = $list->select()->toArray();
        //获取数据总数
        $count = count($list);

        $gameData = BoxGame::field(['id', 'game_name'])->select()->toArray();
        $gameData = array_column($gameData, 'game_name', 'id');

        $xlsData = array();
        if (!empty($list)) {

            $pay_amount_all          = 0;
            $account_num_all         = 0;
            $role_num_all            = 0;
            $role_rate_all           = 0;
            $role_num_all_all        = 0;
            $active_account_num_all  = 0;
            $pay_account_num_all     = 0;
            $active_account_rate_all = 0;
            $arppu_all               = 0;
            $dappu_all               = 0;
            $new_account_num_all     = 0;
            $new_account_amount_all  = 0;
            $new_account_rate_all    = 0;
            $new_account_arppu_all   = 0;

            foreach ($list as $key => $item) {

                $xlsData[$key]['day_time']            = date("Y-m-d", strtotime($item['day_time']));
                $xlsData[$key]['pay_amount']          = $item['pay_amount'];
                $xlsData[$key]['game_name']           = empty($param['box_game_id']) ? '全部游戏' : $gameData[$item['box_game_id']] ?? '';
                $xlsData[$key]['account_num']         = $item['account_num'];
                $xlsData[$key]['role_num']            = $item['role_num'];
                $xlsData[$key]['role_rate']           = empty($item['role_num']) ? '0' : round($item['role_num'] / $item['pay_amount'] * 100, 2) . "%";
                $xlsData[$key]['role_num_all']        = $item['role_num_all'];
                $xlsData[$key]['active_account_num']  = $item['active_account_num'];
                $xlsData[$key]['pay_account_num']     = $item['pay_account_num'];
                $xlsData[$key]['active_account_rate'] = empty($item['active_account_num']) ? '0' : round($item['pay_account_num'] / $item['active_account_num'] * 100, 2) . "%";
                $xlsData[$key]['arppu']               = empty($item['pay_account_num']) ? '0' : round($item['pay_amount'] / $item['pay_account_num'], 2);
                $xlsData[$key]['dappu']               = empty($item['active_account_num']) ? '0' : round($item['pay_amount'] / $item['active_account_num'], 2);
                $xlsData[$key]['new_account_num']     = $item['new_account_num'];
                $xlsData[$key]['new_account_amount']  = $item['new_account_amount'];
                $xlsData[$key]['new_account_rate']    = empty($item['account_num']) ? '0' : round($item['new_account_num'] / $item['account_num'], 2);
                $xlsData[$key]['new_account_arppu']   = empty($item['new_account_num']) ? '0' : round($item['new_account_amount'] / $item['new_account_num'], 2);

                $pay_amount_all          += $item['pay_amount'];
                $account_num_all         += $item['account_num'];
                $role_num_all            += $item['role_num'];
                $role_rate_all           += empty($item['role_num']) ? '0' : round($item['role_num'] / $item['pay_amount'] * 100, 2);
                $role_num_all_all        += $item['role_num_all'];
                $active_account_num_all  += $item['active_account_num'];
                $pay_account_num_all     += $item['pay_account_num'];
                $active_account_rate_all += empty($item['active_account_num']) ? '0' : round($item['pay_account_num'] / $item['active_account_num'] * 100, 2);
                $arppu_all               += empty($item['pay_account_num']) ? '0' : round($item['pay_amount'] / $item['pay_account_num'], 2);
                $dappu_all               += empty($item['active_account_num']) ? '0' : round($item['pay_amount'] / $item['active_account_num'], 2);
                $new_account_num_all     += $item['new_account_num'];
                $new_account_amount_all  += $item['new_account_amount'];
                $new_account_rate_all    += empty($item['account_num']) ? '0' : round($item['new_account_num'] / $item['account_num'], 2);
                $new_account_arppu_all   += empty($item['new_account_num']) ? '0' : round($item['new_account_amount'] / $item['new_account_num'], 2);
            }
            $xlsData[$count]['day_time']            = "汇总";
            $xlsData[$count]['pay_amount']          = $pay_amount_all;
            $xlsData[$count]['game_name']           = "-";
            $xlsData[$count]['account_num']         = $account_num_all;
            $xlsData[$count]['role_num']            = $role_num_all;
            $xlsData[$count]['role_rate']           = round($role_rate_all / $count, 2) . "%";
            $xlsData[$count]['role_num_all']        = $role_num_all_all;
            $xlsData[$count]['active_account_num']  = $active_account_num_all;
            $xlsData[$count]['pay_account_num']     = $pay_account_num_all;
            $xlsData[$count]['active_account_rate'] = round($active_account_rate_all / $count, 2) . "%";
            $xlsData[$count]['arppu']               = round($arppu_all / $count, 2);
            $xlsData[$count]['dappu']               = round($dappu_all / $count, 2);
            $xlsData[$count]['new_account_num']     = $new_account_num_all;
            $xlsData[$count]['new_account_amount']  = $new_account_amount_all;
            $xlsData[$count]['new_account_rate']    = round($new_account_rate_all / $count, 2);
            $xlsData[$count]['new_account_arppu']   = round($new_account_arppu_all / $count, 2);
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }


    /**
     * @Notes:导出数据总表
     * @Interface gameTotalExport
     * @param $map
     */
    public function gameTotalExport($param)
    {
        $xlsName = "游戏总表";
        $xlsCell = array(
            0 => array('game_name', '游戏名称'),
            1 => array('pay_amount', '流水'),
            2 => array('account_num', '新增账号数'),
            3 => array('active_account_num', '活跃账号数量'),
            4 => array('pay_account_num', '付费账号数量'),
        );
        $map     = [];

        if (!empty($param['time_range'])) {
            $param['time_range'][0] = implode('', explode('-', $param['time_range'][0]));
            $param['time_range'][1] = implode('', explode('-', $param['time_range'][1]));
            $map[]                  = ['day_time', '>=', $param['time_range'][0]];
            $map[]                  = ['day_time', '<=', $param['time_range'][1]];
        }
        $list = (new BoxGameLog())
            ->field('box_game_id,sum(pay_amount) as pay_amount,sum(account_num) as account_num')
            ->where($map)
            ->order('box_game_id', 'desc')
            ->group('box_game_id');

        $list = $list->select()->toArray();

        $_gameLoginWhere   = [];
        $_gameLoginWhere[] = ['is_box', '=', 1];
        $_gameLoginWhere[] = ['add_time', 'between', [strtotime($param['time_range'][0]), strtotime($param['time_range'][1] . ' 23:59:59')]];
        //活跃账号数
        $active_account_nums = Db::connect('Db_log')->name('gamelogin_log')->where($_gameLoginWhere)->group('package_id')
            ->column('count(DISTINCT member_id) as active_account_num', 'package_id');

        $_orderWhere   = [];
        $_orderWhere[] = ['is_box', '=', 1];
        $_orderWhere[] = ['pay_time', 'between', [strtotime($param['time_range'][0]), strtotime($param['time_range'][1] . ' 23:59:59')]];
        //付费账号数
        $pay_account_nums = Db::connect('Db_log')->name('order_log')->where($_orderWhere)->group('package_id')
            ->column('count(DISTINCT member_id) as pay_account_num', 'package_id');

        //获取数据总数
        $count = count($list);

        $gameData = BoxGame::field(['id', 'game_name', 'package_id', 'ios_package_id', 'pc_package_id'])->select()->toArray();
        $gameData = array_column($gameData, null, 'id');

        $xlsData = array();
        if (!empty($list)) {

            $pay_amount_all         = 0;
            $account_num_all        = 0;
            $active_account_num_all = 0;
            $pay_account_num_all    = 0;
            foreach ($list as $key => $item) {

                $xlsData[$key]['game_name']          = $gameData[$item['box_game_id']]['game_name'] ?? '';
                $xlsData[$key]['pay_amount']         = $item['pay_amount'];
                $xlsData[$key]['account_num']        = $item['account_num'];
                $xlsData[$key]['active_account_num'] = BoxStatisticsGameOverviewService::iosAndroid($active_account_nums, $gameData[$item['box_game_id']]); //活跃账号数
                $xlsData[$key]['pay_account_num']    = BoxStatisticsGameOverviewService::iosAndroid($pay_account_nums, $gameData[$item['box_game_id']]);      //付费账号数

                $pay_amount_all         += $item['pay_amount'];
                $account_num_all        += $item['account_num'];
                $active_account_num_all += BoxStatisticsGameOverviewService::iosAndroid($active_account_nums, $gameData[$item['box_game_id']]); //活跃账号数
                $pay_account_num_all    += BoxStatisticsGameOverviewService::iosAndroid($pay_account_nums, $gameData[$item['box_game_id']]);      //付费账号数
            }
            $xlsData[$count]['game_name']          = "汇总";
            $xlsData[$count]['pay_amount']         = $pay_amount_all;
            $xlsData[$count]['account_num']        = $account_num_all;
            $xlsData[$count]['active_account_num'] = $active_account_num_all;
            $xlsData[$count]['pay_account_num']    = $pay_account_num_all;
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * @Notes:导出用户留存
     * @Interface gameRemainExport
     * @param $map
     */
    public function gameRemainExport($param, $map)
    {
        $xlsName = "用户留存";
        $xlsCell = array(
            0  => array('day_time', '日期'),
            1  => array('account_num', '新增账号数'),
            2  => array('game_name', '游戏名称'),
            3  => array('d2', '2日留存'),
            4  => array('d3', '3日留存'),
            5  => array('d4', '4日留存'),
            6  => array('d5', '5日留存'),
            7  => array('d6', '6日留存'),
            8  => array('d7', '7日留存'),
            9  => array('d14', '14日留存'),
            10 => array('d30', '30日留存'),
            11 => array('d60', '60日留存'),
            12 => array('d90', '90日留存'),
            13 => array('d180', '180日留存'),
            14 => array('d360', '360日留存'),
        );

        $list = (new BoxGameRemainLog())
            ->field('day_time,box_game_id,sum(account_num) as account_num,sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d60) as d60,sum(d90) as d90,sum(d180) as d180,sum(d360) as d360')
            ->where($map)
            ->order('day_time', 'desc');

        if (empty($param['box_game_id'])) {
            $list->group('day_time');
        } else {
            $list->group('day_time,box_game_id');
        }

        $list = $list->select()->toArray();
        //获取数据总数
        $count = count($list);

        $gameData = BoxGame::field(['id', 'game_name'])->select()->toArray();
        $gameData = array_column($gameData, 'game_name', 'id');

        $xlsData = array();
        if (!empty($list)) {

            $account_num_all = 0;
            $d2_all          = 0;
            $d3_all          = 0;
            $d4_all          = 0;
            $d5_all          = 0;
            $d6_all          = 0;
            $d7_all          = 0;
            $d14_all         = 0;
            $d30_all         = 0;
            $d60_all         = 0;
            $d90_all         = 0;
            $d180_all        = 0;
            $d360_all        = 0;

            foreach ($list as $key => $item) {
                $xlsData[$key]['day_time']    = date("Y-m-d", strtotime($item['day_time']));
                $xlsData[$key]['account_num'] = $item['account_num'];
                $xlsData[$key]['game_name']   = empty($param['box_game_id']) ? '全部游戏' : $gameData[$item['box_game_id']] ?? '';
                $xlsData[$key]['d2']          = empty($item['account_num']) ? "0%" : round($item['d2'] / $item['account_num'] * 100) . "%";
                $xlsData[$key]['d3']          = empty($item['account_num']) ? "0%" : round($item['d3'] / $item['account_num'] * 100) . "%";
                $xlsData[$key]['d4']          = empty($item['account_num']) ? "0%" : round($item['d4'] / $item['account_num'] * 100) . "%";
                $xlsData[$key]['d5']          = empty($item['account_num']) ? "0%" : round($item['d5'] / $item['account_num'] * 100) . "%";
                $xlsData[$key]['d6']          = empty($item['account_num']) ? "0%" : round($item['d6'] / $item['account_num'] * 100) . "%";
                $xlsData[$key]['d7']          = empty($item['account_num']) ? "0%" : round($item['d7'] / $item['account_num'] * 100) . "%";
                $xlsData[$key]['d14']         = empty($item['account_num']) ? "0%" : round($item['d14'] / $item['account_num'] * 100) . "%";
                $xlsData[$key]['d30']         = empty($item['account_num']) ? "0%" : round($item['d30'] / $item['account_num'] * 100) . "%";
                $xlsData[$key]['d60']         = empty($item['account_num']) ? "0%" : round($item['d60'] / $item['account_num'] * 100) . "%";
                $xlsData[$key]['d90']         = empty($item['account_num']) ? "0%" : round($item['d90'] / $item['account_num'] * 100) . "%";
                $xlsData[$key]['d180']        = empty($item['account_num']) ? "0%" : round($item['d180'] / $item['account_num'] * 100) . "%";
                $xlsData[$key]['d360']        = empty($item['account_num']) ? "0%" : round($item['d360'] / $item['account_num'] * 100) . "%";

                $account_num_all += $item['account_num'];
                $d2_all          += empty($item['account_num']) ? "0" : round($item['d2'] / $item['account_num'] * 100);
                $d3_all          += empty($item['account_num']) ? "0" : round($item['d3'] / $item['account_num'] * 100);
                $d4_all          += empty($item['account_num']) ? "0" : round($item['d4'] / $item['account_num'] * 100);
                $d5_all          += empty($item['account_num']) ? "0" : round($item['d5'] / $item['account_num'] * 100);
                $d6_all          += empty($item['account_num']) ? "0" : round($item['d6'] / $item['account_num'] * 100);
                $d7_all          += empty($item['account_num']) ? "0" : round($item['d7'] / $item['account_num'] * 100);
                $d14_all         += empty($item['account_num']) ? "0" : round($item['d14'] / $item['account_num'] * 100);
                $d30_all         += empty($item['account_num']) ? "0" : round($item['d30'] / $item['account_num'] * 100);
                $d60_all         += empty($item['account_num']) ? "0" : round($item['d60'] / $item['account_num'] * 100);
                $d90_all         += empty($item['account_num']) ? "0" : round($item['d90'] / $item['account_num'] * 100);
                $d180_all        += empty($item['account_num']) ? "0" : round($item['d180'] / $item['account_num'] * 100);
                $d360_all        += empty($item['account_num']) ? "0" : round($item['d360'] / $item['account_num'] * 100);
            }
            $xlsData[$count]['day_time']    = "汇总";
            $xlsData[$count]['account_num'] = $account_num_all;
            $xlsData[$count]['game_name']   = "-";
            $xlsData[$count]['d2']          = empty($d2_all) ? '0%' : round($d2_all / $count) . "%";
            $xlsData[$count]['d3']          = empty($d3_all) ? '0%' : round($d3_all / $count) . "%";
            $xlsData[$count]['d4']          = empty($d4_all) ? '0%' : round($d4_all / $count) . "%";
            $xlsData[$count]['d5']          = empty($d5_all) ? '0%' : round($d5_all / $count) . "%";
            $xlsData[$count]['d6']          = empty($d6_all) ? '0%' : round($d6_all / $count) . "%";
            $xlsData[$count]['d7']          = empty($d7_all) ? '0%' : round($d7_all / $count) . "%";
            $xlsData[$count]['d14']         = empty($d14_all) ? '0%' : round($d14_all / $count) . "%";
            $xlsData[$count]['d30']         = empty($d30_all) ? '0%' : round($d30_all / $count) . "%";
            $xlsData[$count]['d60']         = empty($d60_all) ? '0%' : round($d60_all / $count) . "%";
            $xlsData[$count]['d90']         = empty($d90_all) ? '0%' : round($d90_all / $count) . "%";
            $xlsData[$count]['d180']        = empty($d180_all) ? '0%' : round($d180_all / $count) . "%";
            $xlsData[$count]['d360']        = empty($d360_all) ? '0%' : round($d360_all / $count) . "%";
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }


    /**
     * @Notes:导出付费用户留存
     * @Interface gameMpayRemainExport
     * @param $map
     */
    public function gameMpayRemainExport($param, $map)
    {
        $xlsName = "付费用户留存";
        $xlsCell = array(
            0  => array('day_time', '日期'),
            1  => array('pay_account_num', '新增账号付费数'),
            2  => array('game_name', '游戏名称'),
            3  => array('d2', '2日留存'),
            4  => array('d3', '3日留存'),
            5  => array('d4', '4日留存'),
            6  => array('d5', '5日留存'),
            7  => array('d6', '6日留存'),
            8  => array('d7', '7日留存'),
            9  => array('d14', '14日留存'),
            10 => array('d30', '30日留存'),
            11 => array('d60', '60日留存'),
            12 => array('d90', '90日留存'),
            13 => array('d180', '180日留存'),
            14 => array('d360', '360日留存'),
        );

        $list = (new BoxMpayRemainLog())
            ->field('day_time,box_game_id,sum(pay_account_num) as pay_account_num,sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d60) as d60,sum(d90) as d90,sum(d180) as d180,sum(d360) as d360')
            ->where($map)
            ->order('day_time', 'desc');

        if (empty($param['box_game_id'])) {
            $list->group('day_time');
        } else {
            $list->group('day_time,box_game_id');
        }

        $list = $list->select()->toArray();
        //获取数据总数
        $count = count($list);

        $gameData = BoxGame::field(['id', 'game_name'])->select()->toArray();
        $gameData = array_column($gameData, 'game_name', 'id');

        $xlsData = array();
        if (!empty($list)) {

            $pay_account_num_all = 0;
            $d2_all              = 0;
            $d3_all              = 0;
            $d4_all              = 0;
            $d5_all              = 0;
            $d6_all              = 0;
            $d7_all              = 0;
            $d14_all             = 0;
            $d30_all             = 0;
            $d60_all             = 0;
            $d90_all             = 0;
            $d180_all            = 0;
            $d360_all            = 0;

            foreach ($list as $key => $item) {
                $xlsData[$key]['day_time']        = date("Y-m-d", strtotime($item['day_time']));
                $xlsData[$key]['pay_account_num'] = $item['pay_account_num'];
                $xlsData[$key]['game_name']       = empty($param['box_game_id']) ? '全部游戏' : $gameData[$item['box_game_id']] ?? '';
                $xlsData[$key]['d2']              = empty($item['pay_account_num']) ? "0%" : round($item['d2'] / $item['pay_account_num'] * 100) . "%";
                $xlsData[$key]['d3']              = empty($item['pay_account_num']) ? "0%" : round($item['d3'] / $item['pay_account_num'] * 100) . "%";
                $xlsData[$key]['d4']              = empty($item['pay_account_num']) ? "0%" : round($item['d4'] / $item['pay_account_num'] * 100) . "%";
                $xlsData[$key]['d5']              = empty($item['pay_account_num']) ? "0%" : round($item['d5'] / $item['pay_account_num'] * 100) . "%";
                $xlsData[$key]['d6']              = empty($item['pay_account_num']) ? "0%" : round($item['d6'] / $item['pay_account_num'] * 100) . "%";
                $xlsData[$key]['d7']              = empty($item['pay_account_num']) ? "0%" : round($item['d7'] / $item['pay_account_num'] * 100) . "%";
                $xlsData[$key]['d14']             = empty($item['pay_account_num']) ? "0%" : round($item['d14'] / $item['pay_account_num'] * 100) . "%";
                $xlsData[$key]['d30']             = empty($item['pay_account_num']) ? "0%" : round($item['d30'] / $item['pay_account_num'] * 100) . "%";
                $xlsData[$key]['d60']             = empty($item['pay_account_num']) ? "0%" : round($item['d60'] / $item['pay_account_num'] * 100) . "%";
                $xlsData[$key]['d90']             = empty($item['pay_account_num']) ? "0%" : round($item['d90'] / $item['pay_account_num'] * 100) . "%";
                $xlsData[$key]['d180']            = empty($item['pay_account_num']) ? "0%" : round($item['d180'] / $item['pay_account_num'] * 100) . "%";
                $xlsData[$key]['d360']            = empty($item['pay_account_num']) ? "0%" : round($item['d360'] / $item['pay_account_num'] * 100) . "%";

                $pay_account_num_all += $item['pay_account_num'];
                $d2_all              += empty($item['pay_account_num']) ? "0" : round($item['d2'] / $item['pay_account_num'] * 100);
                $d3_all              += empty($item['pay_account_num']) ? "0" : round($item['d3'] / $item['pay_account_num'] * 100);
                $d4_all              += empty($item['pay_account_num']) ? "0" : round($item['d4'] / $item['pay_account_num'] * 100);
                $d5_all              += empty($item['pay_account_num']) ? "0" : round($item['d5'] / $item['pay_account_num'] * 100);
                $d6_all              += empty($item['pay_account_num']) ? "0" : round($item['d6'] / $item['pay_account_num'] * 100);
                $d7_all              += empty($item['pay_account_num']) ? "0" : round($item['d7'] / $item['pay_account_num'] * 100);
                $d14_all             += empty($item['pay_account_num']) ? "0" : round($item['d14'] / $item['pay_account_num'] * 100);
                $d30_all             += empty($item['pay_account_num']) ? "0" : round($item['d30'] / $item['pay_account_num'] * 100);
                $d60_all             += empty($item['pay_account_num']) ? "0" : round($item['d60'] / $item['pay_account_num'] * 100);
                $d90_all             += empty($item['pay_account_num']) ? "0" : round($item['d90'] / $item['pay_account_num'] * 100);
                $d180_all            += empty($item['pay_account_num']) ? "0" : round($item['d180'] / $item['pay_account_num'] * 100);
                $d360_all            += empty($item['pay_account_num']) ? "0" : round($item['d360'] / $item['pay_account_num'] * 100);
            }
            $xlsData[$count]['day_time']        = "汇总";
            $xlsData[$count]['pay_account_num'] = $pay_account_num_all;
            $xlsData[$count]['game_name']       = "-";
            $xlsData[$count]['d2']              = empty($d2_all) ? '0%' : round($d2_all / $count) . "%";
            $xlsData[$count]['d3']              = empty($d3_all) ? '0%' : round($d3_all / $count) . "%";
            $xlsData[$count]['d4']              = empty($d4_all) ? '0%' : round($d4_all / $count) . "%";
            $xlsData[$count]['d5']              = empty($d5_all) ? '0%' : round($d5_all / $count) . "%";
            $xlsData[$count]['d6']              = empty($d6_all) ? '0%' : round($d6_all / $count) . "%";
            $xlsData[$count]['d7']              = empty($d7_all) ? '0%' : round($d7_all / $count) . "%";
            $xlsData[$count]['d14']             = empty($d14_all) ? '0%' : round($d14_all / $count) . "%";
            $xlsData[$count]['d30']             = empty($d30_all) ? '0%' : round($d30_all / $count) . "%";
            $xlsData[$count]['d60']             = empty($d60_all) ? '0%' : round($d60_all / $count) . "%";
            $xlsData[$count]['d90']             = empty($d90_all) ? '0%' : round($d90_all / $count) . "%";
            $xlsData[$count]['d180']            = empty($d180_all) ? '0%' : round($d180_all / $count) . "%";
            $xlsData[$count]['d360']            = empty($d360_all) ? '0%' : round($d360_all / $count) . "%";
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }


    /**
     * @Notes:导出持续付费率
     * @Interface gameMemberpayRateExport
     * @param $map
     */
    public function gameMemberpayRateExport($param, $map)
    {
        $xlsName = "持续付费率";
        $xlsCell = array(
            0  => array('day_time', '日期'),
            1  => array('pay_account_num', '新增账号付费数'),
            2  => array('game_name', '游戏名称'),
            3  => array('d2', '2日留存'),
            4  => array('d3', '3日留存'),
            5  => array('d4', '4日留存'),
            6  => array('d5', '5日留存'),
            7  => array('d6', '6日留存'),
            8  => array('d7', '7日留存'),
            9  => array('d14', '14日留存'),
            10 => array('d30', '30日留存'),
            11 => array('d60', '60日留存'),
        );

        $list = (new BoxMemberpayRateLog())
            ->field('day_time,box_game_id,sum(pay_account_num) as pay_account_num,sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d60) as d60')
            ->where($map)
            ->order('day_time', 'desc');

        if (empty($param['box_game_id'])) {
            $list->group('day_time');
        } else {
            $list->group('day_time,box_game_id');
        }

        $list = $list->select()->toArray();
        //获取数据总数
        $count = count($list);

        $gameData = BoxGame::field(['id', 'game_name'])->select()->toArray();
        $gameData = array_column($gameData, 'game_name', 'id');

        $xlsData = array();
        if (!empty($list)) {

            $pay_account_num_all = 0;
            $d2_all              = 0;
            $d3_all              = 0;
            $d4_all              = 0;
            $d5_all              = 0;
            $d6_all              = 0;
            $d7_all              = 0;
            $d14_all             = 0;
            $d30_all             = 0;
            $d60_all             = 0;

            foreach ($list as $key => $item) {
                $xlsData[$key]['day_time']        = date("Y-m-d", strtotime($item['day_time']));
                $xlsData[$key]['pay_account_num'] = $item['pay_account_num'];
                $xlsData[$key]['game_name']       = empty($param['box_game_id']) ? '全部游戏' : $gameData[$item['box_game_id']] ?? '';
                $xlsData[$key]['d2']              = empty($item['pay_account_num']) ? "0%" : round($item['d2'] / $item['pay_account_num'] * 100) . "%";
                $xlsData[$key]['d3']              = empty($item['pay_account_num']) ? "0%" : round($item['d3'] / $item['pay_account_num'] * 100) . "%";
                $xlsData[$key]['d4']              = empty($item['pay_account_num']) ? "0%" : round($item['d4'] / $item['pay_account_num'] * 100) . "%";
                $xlsData[$key]['d5']              = empty($item['pay_account_num']) ? "0%" : round($item['d5'] / $item['pay_account_num'] * 100) . "%";
                $xlsData[$key]['d6']              = empty($item['pay_account_num']) ? "0%" : round($item['d6'] / $item['pay_account_num'] * 100) . "%";
                $xlsData[$key]['d7']              = empty($item['pay_account_num']) ? "0%" : round($item['d7'] / $item['pay_account_num'] * 100) . "%";
                $xlsData[$key]['d14']             = empty($item['pay_account_num']) ? "0%" : round($item['d14'] / $item['pay_account_num'] * 100) . "%";
                $xlsData[$key]['d30']             = empty($item['pay_account_num']) ? "0%" : round($item['d30'] / $item['pay_account_num'] * 100) . "%";
                $xlsData[$key]['d60']             = empty($item['pay_account_num']) ? "0%" : round($item['d60'] / $item['pay_account_num'] * 100) . "%";

                $pay_account_num_all += $item['pay_account_num'];
                $d2_all              += empty($item['pay_account_num']) ? "0" : round($item['d2'] / $item['pay_account_num'] * 100);
                $d3_all              += empty($item['pay_account_num']) ? "0" : round($item['d3'] / $item['pay_account_num'] * 100);
                $d4_all              += empty($item['pay_account_num']) ? "0" : round($item['d4'] / $item['pay_account_num'] * 100);
                $d5_all              += empty($item['pay_account_num']) ? "0" : round($item['d5'] / $item['pay_account_num'] * 100);
                $d6_all              += empty($item['pay_account_num']) ? "0" : round($item['d6'] / $item['pay_account_num'] * 100);
                $d7_all              += empty($item['pay_account_num']) ? "0" : round($item['d7'] / $item['pay_account_num'] * 100);
                $d14_all             += empty($item['pay_account_num']) ? "0" : round($item['d14'] / $item['pay_account_num'] * 100);
                $d30_all             += empty($item['pay_account_num']) ? "0" : round($item['d30'] / $item['pay_account_num'] * 100);
                $d60_all             += empty($item['pay_account_num']) ? "0" : round($item['d60'] / $item['pay_account_num'] * 100);
            }
            $xlsData[$count]['day_time']        = "汇总";
            $xlsData[$count]['pay_account_num'] = $pay_account_num_all;
            $xlsData[$count]['game_name']       = "-";
            $xlsData[$count]['d2']              = empty($d2_all) ? '0%' : round($d2_all / $count) . "%";
            $xlsData[$count]['d3']              = empty($d3_all) ? '0%' : round($d3_all / $count) . "%";
            $xlsData[$count]['d4']              = empty($d4_all) ? '0%' : round($d4_all / $count) . "%";
            $xlsData[$count]['d5']              = empty($d5_all) ? '0%' : round($d5_all / $count) . "%";
            $xlsData[$count]['d6']              = empty($d6_all) ? '0%' : round($d6_all / $count) . "%";
            $xlsData[$count]['d7']              = empty($d7_all) ? '0%' : round($d7_all / $count) . "%";
            $xlsData[$count]['d14']             = empty($d14_all) ? '0%' : round($d14_all / $count) . "%";
            $xlsData[$count]['d30']             = empty($d30_all) ? '0%' : round($d30_all / $count) . "%";
            $xlsData[$count]['d60']             = empty($d60_all) ? '0%' : round($d60_all / $count) . "%";
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }


    /**
     * @Notes:导出用户LTV
     * @Interface gameMemberltvExport
     * @param $map
     */
    public function gameMemberltvExport($param, $map)
    {
        $xlsName = "用户LTV";
        $xlsCell = array(
            0  => array('day_time', '日期'),
            1  => array('account_num', '新增账号数'),
            2  => array('game_name', '游戏名称'),
            3  => array('total_ltv', '汇总LTV'),
            4  => array('d1', 'D1LTV'),
            5  => array('d2', 'D2LTV'),
            6  => array('d3', 'D3LTV'),
            7  => array('d4', 'D4LTV'),
            8  => array('d5', 'D5LTV'),
            9  => array('d6', 'D6LTV'),
            10 => array('d7', 'D7LTV'),
            11 => array('d14', 'D14LTV'),
            12 => array('d30', 'D30LTV'),
            13 => array('d45', 'D45LTV'),
            14 => array('d60', 'D60LTV'),
            15 => array('d90', 'D90LTV'),
            16 => array('d120', 'D120LTV'),
            17 => array('d180', 'D180LTV'),
            18 => array('d360', 'D360LTV'),
        );

        $list = (new BoxMemberltvLog())
            ->field('day_time,box_game_id,sum(account_num) as account_num,sum(d1) as d1,sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d45) as d45,sum(d60) as d60,sum(d90) as d90,sum(d120) as d120,sum(d180) as d180,sum(d360) as d360')
            ->where($map)
            ->order('day_time', 'desc');

        if (empty($param['box_game_id'])) {
            $list->group('day_time');
        } else {
            $list->group('day_time,box_game_id');
        }

        $list = $list->select()->toArray();
        //获取数据总数
        $count = count($list);

        $gameData = BoxGame::field(['id', 'game_name'])->select()->toArray();
        $gameData = array_column($gameData, 'game_name', 'id');

        $xlsData = array();
        if (!empty($list)) {

            $account_num_all = 0;
            $total_ltv       = 0;
            $d1_all          = 0;
            $d2_all          = 0;
            $d3_all          = 0;
            $d4_all          = 0;
            $d5_all          = 0;
            $d6_all          = 0;
            $d7_all          = 0;
            $d14_all         = 0;
            $d30_all         = 0;
            $d45_all         = 0;
            $d60_all         = 0;
            $d90_all         = 0;
            $d120_all        = 0;
            $d180_all        = 0;
            $d360_all        = 0;

            foreach ($list as $key => $item) {
                $day     = diffTimeRetrunDay(strtotime($item['day_time']), strtotime(date("Y-m-d")));
                $day     = $day + 1;
                $dayList = ['1', '2', '3', '4', '5', '6', '7', '14', '30', '45', '60', '90', '120', '180', '360'];
                $dayNum  = findValue($day, $dayList);
                $dayNum  = "d" . $dayNum;

                $xlsData[$key]['day_time']    = date("Y-m-d", strtotime($item['day_time']));
                $xlsData[$key]['account_num'] = $item['account_num'];
                $xlsData[$key]['game_name']   = empty($param['box_game_id']) ? '全部游戏' : $gameData[$item['box_game_id']] ?? '';
                $xlsData[$key]['total_ltv']   = empty($item['account_num']) ? "0" : round($item[$dayNum] / $item['account_num'], 2);

                $num = findMinValue($day, $dayList);
                foreach ($dayList as $dayItem) {
                    $dayItemNum = "d" . $dayItem;
                    if ($dayItem <= $num) {
                        $xlsData[$key][$dayItemNum] = empty($item['account_num']) ? "0" : round($item[$dayItemNum] / $item['account_num'], 2);
                    } else {
                        $xlsData[$key][$dayItemNum] = '0';
                    }
                }

                $account_num_all += $item['account_num'];
                $total_ltv       += $xlsData[$key]['total_ltv'];
                $d1_all          += $xlsData[$key]['d1'];
                $d2_all          += $xlsData[$key]['d2'];
                $d3_all          += $xlsData[$key]['d3'];
                $d4_all          += $xlsData[$key]['d4'];
                $d5_all          += $xlsData[$key]['d5'];
                $d6_all          += $xlsData[$key]['d6'];
                $d7_all          += $xlsData[$key]['d7'];
                $d14_all         += $xlsData[$key]['d14'];
                $d30_all         += $xlsData[$key]['d30'];
                $d45_all         += $xlsData[$key]['d45'];
                $d60_all         += $xlsData[$key]['d60'];
                $d90_all         += $xlsData[$key]['d90'];
                $d120_all        += $xlsData[$key]['d120'];
                $d180_all        += $xlsData[$key]['d180'];
                $d360_all        += $xlsData[$key]['d360'];
            }
            $xlsData[$count]['day_time']    = "汇总";
            $xlsData[$count]['account_num'] = $account_num_all;
            $xlsData[$count]['game_name']   = "-";
            $xlsData[$count]['total_ltv']   = empty($total_ltv) ? '0' : round($total_ltv / $count, 2);
            $xlsData[$count]['d1']          = empty($d1_all) ? '0' : round($d1_all / $count, 2);
            $xlsData[$count]['d2']          = empty($d2_all) ? '0' : round($d2_all / $count, 2);
            $xlsData[$count]['d3']          = empty($d3_all) ? '0' : round($d3_all / $count, 2);
            $xlsData[$count]['d4']          = empty($d4_all) ? '0' : round($d4_all / $count, 2);
            $xlsData[$count]['d5']          = empty($d5_all) ? '0' : round($d5_all / $count, 2);
            $xlsData[$count]['d6']          = empty($d6_all) ? '0' : round($d6_all / $count, 2);
            $xlsData[$count]['d7']          = empty($d7_all) ? '0' : round($d7_all / $count, 2);
            $xlsData[$count]['d14']         = empty($d14_all) ? '0' : round($d14_all / $count, 2);
            $xlsData[$count]['d30']         = empty($d30_all) ? '0' : round($d30_all / $count, 2);
            $xlsData[$count]['d45']         = empty($d45_all) ? '0' : round($d45_all / $count, 2);
            $xlsData[$count]['d60']         = empty($d60_all) ? '0' : round($d60_all / $count, 2);
            $xlsData[$count]['d90']         = empty($d90_all) ? '0' : round($d90_all / $count, 2);
            $xlsData[$count]['d120']        = empty($d120_all) ? '0' : round($d120_all / $count, 2);
            $xlsData[$count]['d180']        = empty($d180_all) ? '0' : round($d180_all / $count, 2);
            $xlsData[$count]['d360']        = empty($d360_all) ? '0' : round($d360_all / $count, 2);
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }


    /**
     * @Notes:导出付费用户LTV
     * @Interface gameMemberltvExport
     * @param $map
     */
    public function gameMemberpayltvExport($param, $map)
    {
        $xlsName = "付费用户LTV";
        $xlsCell = array(
            0  => array('day_time', '日期'),
            1  => array('pay_account_num', '新增付费账号数'),
            2  => array('game_name', '游戏名称'),
            3  => array('total_ltv', '汇总LTV'),
            4  => array('d1', 'D1LTV'),
            5  => array('d2', 'D2LTV'),
            6  => array('d3', 'D3LTV'),
            7  => array('d4', 'D4LTV'),
            8  => array('d5', 'D5LTV'),
            9  => array('d6', 'D6LTV'),
            10 => array('d7', 'D7LTV'),
            11 => array('d14', 'D14LTV'),
            12 => array('d30', 'D30LTV'),
            13 => array('d45', 'D45LTV'),
            14 => array('d60', 'D60LTV'),
            15 => array('d90', 'D90LTV'),
            16 => array('d120', 'D120LTV'),
            17 => array('d180', 'D180LTV'),
            18 => array('d360', 'D360LTV'),
        );

        $list = (new BoxMemberpayLtvLog())
            ->field('day_time,box_game_id,sum(pay_account_num) as pay_account_num,sum(d1) as d1,sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d45) as d45,sum(d60) as d60,sum(d90) as d90,sum(d120) as d120,sum(d180) as d180,sum(d360) as d360')
            ->where($map)
            ->order('day_time', 'desc');

        if (empty($param['box_game_id'])) {
            $list->group('day_time');
        } else {
            $list->group('day_time,box_game_id');
        }

        $list = $list->select()->toArray();
        //获取数据总数
        $count = count($list);

        $gameData = BoxGame::field(['id', 'game_name'])->select()->toArray();
        $gameData = array_column($gameData, 'game_name', 'id');

        $xlsData = array();
        if (!empty($list)) {

            $pay_account_num_all = 0;
            $total_ltv           = 0;
            $d1_all              = 0;
            $d2_all              = 0;
            $d3_all              = 0;
            $d4_all              = 0;
            $d5_all              = 0;
            $d6_all              = 0;
            $d7_all              = 0;
            $d14_all             = 0;
            $d30_all             = 0;
            $d45_all             = 0;
            $d60_all             = 0;
            $d90_all             = 0;
            $d120_all            = 0;
            $d180_all            = 0;
            $d360_all            = 0;

            foreach ($list as $key => $item) {
                $day     = diffTimeRetrunDay(strtotime($item['day_time']), strtotime(date("Y-m-d")));
                $day     = $day + 1;
                $dayList = ['1', '2', '3', '4', '5', '6', '7', '14', '30', '45', '60', '90', '120', '180', '360'];
                $dayNum  = findValue($day, $dayList);
                $dayNum  = "d" . $dayNum;

                $xlsData[$key]['day_time']        = date("Y-m-d", strtotime($item['day_time']));
                $xlsData[$key]['pay_account_num'] = $item['pay_account_num'];
                $xlsData[$key]['game_name']       = empty($param['box_game_id']) ? '全部游戏' : $gameData[$item['box_game_id']] ?? '';
                $xlsData[$key]['total_ltv']       = empty($item['pay_account_num']) ? "0" : round($item[$dayNum] / $item['pay_account_num'], 2);

                $num = findMinValue($day, $dayList);
                foreach ($dayList as $dayItem) {
                    $dayItemNum = "d" . $dayItem;
                    if ($dayItem <= $num) {
                        $xlsData[$key][$dayItemNum] = empty($item['pay_account_num']) ? "0" : round($item[$dayItemNum] / $item['pay_account_num'], 2);
                    } else {
                        $xlsData[$key][$dayItemNum] = '0';
                    }
                }

                $pay_account_num_all += $xlsData[$key]['pay_account_num'];
                $total_ltv           += $xlsData[$key]['total_ltv'];
                $d1_all              += $xlsData[$key]['d1'];
                $d2_all              += $xlsData[$key]['d2'];
                $d3_all              += $xlsData[$key]['d3'];
                $d4_all              += $xlsData[$key]['d4'];
                $d5_all              += $xlsData[$key]['d5'];
                $d6_all              += $xlsData[$key]['d6'];
                $d7_all              += $xlsData[$key]['d7'];
                $d14_all             += $xlsData[$key]['d14'];
                $d30_all             += $xlsData[$key]['d30'];
                $d45_all             += $xlsData[$key]['d45'];
                $d60_all             += $xlsData[$key]['d60'];
                $d90_all             += $xlsData[$key]['d90'];
                $d120_all            += $xlsData[$key]['d120'];
                $d180_all            += $xlsData[$key]['d180'];
                $d360_all            += $xlsData[$key]['d360'];
            }
            $xlsData[$count]['day_time']        = "汇总";
            $xlsData[$count]['pay_account_num'] = $pay_account_num_all;
            $xlsData[$count]['game_name']       = "-";
            $xlsData[$count]['total_ltv']       = empty($total_ltv) ? '0' : round($total_ltv / $count, 2) ?? '0';
            $xlsData[$count]['d1']              = empty($d1_all) ? '0' : round($d1_all / $count, 2) ?? '0';
            $xlsData[$count]['d2']              = empty($d2_all) ? '0' : round($d2_all / $count, 2) ?? '0';
            $xlsData[$count]['d3']              = empty($d3_all) ? '0' : round($d3_all / $count, 2) ?? '0';
            $xlsData[$count]['d4']              = empty($d4_all) ? '0' : round($d4_all / $count, 2) ?? '0';
            $xlsData[$count]['d5']              = empty($d5_all) ? '0' : round($d5_all / $count, 2) ?? '0';
            $xlsData[$count]['d6']              = empty($d6_all) ? '0' : round($d6_all / $count, 2) ?? '0';
            $xlsData[$count]['d7']              = empty($d7_all) ? '0' : round($d7_all / $count, 2) ?? '0';
            $xlsData[$count]['d14']             = empty($d14_all) ? '0' : round($d14_all / $count, 2) ?? '0';
            $xlsData[$count]['d30']             = empty($d30_all) ? '0' : round($d30_all / $count, 2) ?? '0';
            $xlsData[$count]['d45']             = empty($d45_all) ? '0' : round($d45_all / $count, 2) ?? '0';
            $xlsData[$count]['d60']             = empty($d60_all) ? '0' : round($d60_all / $count, 2) ?? '0';
            $xlsData[$count]['d90']             = empty($d90_all) ? '0' : round($d90_all / $count, 2) ?? '0';
            $xlsData[$count]['d120']            = empty($d120_all) ? '0' : round($d120_all / $count, 2) ?? '0';
            $xlsData[$count]['d180']            = empty($d180_all) ? '0' : round($d180_all / $count, 2) ?? '0';
            $xlsData[$count]['d360']            = empty($d360_all) ? '0' : round($d360_all / $count, 2) ?? '0';
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }


    /**
     * Desc : 云挂机CDK导出
     * User : Steven
     * Date : 2024-05-22 17:05
     * @param $map
     */
    public function yunCdkExport($map)
    {
        $xlsName = "云挂机CDK导出";
        $xlsCell = array(
            0 => array('id', 'ID'),
            1 => array('batch_id', '批次ID'),
            2 => array('member_id', '领取人ID'),
            3 => array('cdk', 'CDK码'),
            4 => array('is_active', '是否激活(0=未激活,1=激活)'),
            5 => array('status', '是否开启(1=启用,2=停用)'),
            6 => array('use_time', '激活时间'),
            7 => array('start_time', '开始时间'),
            8 => array('end_time', '结束时间'),
        );
        $xlsData = (new YunPhoneCdkCode())->field('id,batch_id,member_id,cdk,is_active,status,use_time,start_time,end_time')->where($map)->order('id asc')->select()->toArray();
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }


    /**
     * @Notes:云挂机用户设备导出
     * @Interface BoxYunUserMachine
     * @param $where
     */
    public function BoxYunUserMachine($where)
    {
        $xlsName = "云挂机用户设备导出";
        $xlsCell = array(
            0 => array('user_phone_id', 'userPhoneId'),
            1 => array('member_id', '用户编号'),
            2 => array('valid_start_time', '绑定时间'),
            3 => array('valid_end_time', '过期时间'),
            4 => array('name', '设备别名'),
            5 => array('running_status_name', '设备状态'),
        );
        $xlsData = BoxYunUserMachine::where($where)->order('id', 'desc')->select();
        foreach ($xlsData as &$item) {
            $item['running_status_name'] = BoxYunUserMachine::$running_status[$item['running_status']];
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * Desc : 免费领取报表导出
     * User : Steven
     * Date : 2024-05-27 10:14
     * @param $where
     */
    public function yunFreeGetLogExport($where)
    {
        $xlsName = "云挂机免费领取报表导出";
        $xlsCell = array(
            0 => array('day_time', '日期'),
            1 => array('get_account_num', '领取用户数'),
            2 => array('box_active_account_num', '盒子活跃用户数'),
            3 => array('get_duration', '领取总时长(单位:小时)'),
            4 => array('get_pay_account_num', '领取用户付费数'),
            5 => array('get_pay_amount', '领取云挂机付费总金额'),
            6 => array('get_machine_num', '领取用户续费设备数'),
            7 => array('get_yun_pay_amount', '领取用户续费总金额'),
        );
        $xlsData = BoxYunFreeGetLog::where($where)->order('day_time', 'desc')->select();
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }


    /**
     * Desc : 云挂机商城报表导出
     * User : Steven
     * Date : 2024-05-27 10:19
     * @param $where
     */
    public function yunGoodLogExport($where)
    {
        $xlsName = "云挂机商城报表导出";
        $xlsCell = array(
            0 => array('day_time', '日期'),
            1 => array('run_water', '流水(单位:元)'),
            2 => array('pay_amount', '实付金额'),
            3 => array('good_cost', '商品成本'),
            4 => array('review', '商品浏览数'),
            5 => array('order_num', '商品下单数'),
            6 => array('pay_order_num', '商品付款订单数'),
            7 => array('cancel_order_num', '取消订单数'),
            8 => array('refund_order_num', '退款订单数'),
            9 => array('refund_amount', '退款总额'),
        );
        $xlsData = BoxYunGoodLog::where($where)->order('day_time', 'desc')->select();
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }


    /**
     * Desc : 下载模版
     * User : Steven
     * Date : 2024-06-20 17:42
     * @param $where
     */
    public function wallLogExport($where)
    {
        $xlsName = "IDF模板(将idfa填入此模版)";
        $xlsCell = array(
            0 => array('wTime', '设备执行时间(如:20241001)'),
            1 => array('idfa', '设备IDFA'),
        );
        $xlsData = [];
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * Desc : 渠道总览导出
     * User : Steven
     * Date : 2024-07-01 14:40
     * @param $param
     */
    public function expPromotePackagePayRateExport($param)
    {
        $type        = $param['type'];
        $xlsCellType = [];
        $xlsName     = '';
        if ($type == 2) {
            $xlsName     = "渠道总览导出";
            $xlsCellType = array(
                array('promote_realname', '推广员'),
            );
        } elseif ($type == 1) {
            $xlsName     = "游戏总览导出";
            $xlsCellType = array(
                array('package_name', '游戏包名称'),
            );
        } elseif ($type == 3) {
            $xlsName     = "分游戏每日总览导出";
            $xlsCellType = array(
                array('record_date', '日期'),
                array('promote_realname', '推广员'),
                array('package_name', '游戏包名称'),
            );
        }
        $xlsCell = array(
            array('register_num', '新增用户数'),
            array('pay_amount', '付费总金额'),
            array('login_num', '活跃用户数'),
            array('register_device_num', '新增激活设备'),
            array('pay_user_num', '付费用户数'),
            array('login_device_num', '活跃设备'),
            array('register_role_num', '新增角色'),
            array('new_pay_user_num', '新增付费用户'),
            array('new_amount', '新增付费金额'),
            array('darpu', 'darpu'),
            array('arppu', 'arppu'),
            array('pay_ratio', '用户付费率'),
            array('new_pay_ratio', '新增用户付费率'),
        );
        $xlsCell = array_merge($xlsCellType, $xlsCell);

        if (!empty($param['promote_id'])) {
            $map[] = ['promote_id', 'in', $param['promote_id']];
        }
        if (!empty($param['time_range'])) {
            // $param['time_range'][0] = implode('', explode('-', $param['time_range'][0]));
            // $param['time_range'][1] = implode('', explode('-', $param['time_range'][1]));
            $map[] = ['record_date', '>=', $param['time_range'][0]];
            $map[] = ['record_date', '<=', $param['time_range'][1]];
        }
        if (!empty($param['package_id'])) {
            $map[] = ['package_id', 'in', $param['package_id']];
        }
        if (!empty($param['register_source'])) {
            $map[] = ['register_source', '=', $param['register_source']];
        }


        // 游戏总览
        if ($type == 1) {
            $group   = 'package_id';
            $order   = 'pay_amount desc';
            $package = (new Package())->column('package_name', 'id');
        }
        // 推广渠道总览
        if ($type == 2) {
            $group   = 'promote_id';
            $order   = 'pay_amount desc';
            $promote = (new UserPromote())->column('realname', 'id');
        }
        // 每日总览
        if ($type == 3) {
            $group = 'record_date';
        }

        $order                   = 'record_date desc';
        $PromotePackageStatsList = (new PromotePackageStats)
            ->field("id,record_date,promote_id,package_id,sum(register_num) as register_num,sum(pay_amount) as pay_amount,sum(login_num) as login_num,sum(register_device_num) as register_device_num,sum(pay_user_num) as pay_user_num,sum(login_device_num) as login_device_num,sum(register_role_num) as register_role_num,sum(new_pay_user_num) as new_pay_user_num,sum(new_amount) as new_amount")
            ->where($map)
            ->group($group)
            ->order($order)
            ->select();
        $xlsData                 = [];
        foreach ($PromotePackageStatsList as $key => &$val) {
            $val['darpu']         = $val['login_num'] > 0 ? round($val['pay_amount'] / $val['login_num'], 2) : '0';
            $val['arppu']         = $val['pay_user_num'] > 0 ? round($val['pay_amount'] / $val['pay_user_num'], 2) : '0';
            $val['pay_ratio']     = $val['login_num'] > 0 ? round(($val['pay_user_num'] / $val['login_num']) * 100, 2) . '%' : '0%';
            $val['new_pay_ratio'] = $val['register_num'] > 0 ? round(($val['new_pay_user_num'] / $val['register_num']) * 100, 2) . '%' : '0%';
            if ($type == 1) {
                $val['package_name'] = isset($package[$val['package_id']]) ? $package[$val['package_id']] : "";
                if ($val['package_id'] == 9999998) {
                    $val['package_name'] = '游戏盒子(IOS)';
                }
                if ($val['package_id'] == 9999999) {
                    $val['package_name'] = '游戏盒子(安卓)';
                }
            }
            if ($type == 2) {
                $val['promote_realname'] = isset($promote[$val['promote_id']]) ? $promote[$val['promote_id']] : "";
            }
            if ($type == 3) {
                $val['promote_realname'] = '全部渠道';
                $val['package_name']     = "全部游戏";
                if (!empty($param['promote_id'])) {
                    if (count($param['promote_id']) > 1) {
                        $val['promote_realname'] = "多个渠道";
                    } else {
                        $val['promote_realname'] = (new UserPromote())->whereIn('id', $param['promote_id'])->value('realname');
                    }
                }
                if (!empty($param['package_id'])) {
                    if (count($param['package_id']) > 1) {
                        $val['package_name'] = "多个游戏";
                    } else {
                        $val['package_name'] = (new Package())->whereIn('id', $param['package_id'])->value('package_name');
                    }
                }
            }
            unset($val['id']);
            unset($val['promote_id']);
            unset($val['package_id']);
            // 移除数组的最后一个元素并保存它
            $val = $val->toArray();
            end($val);
            $lastKey          = key($val);
            $lastKeyValuePair = array($lastKey => $val[$lastKey]);
            unset($val[$lastKey]);
            $val           = array_merge($lastKeyValuePair, $val);
            $xlsData[$key] = $val;
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }


    /**
     * Desc : 用户留存
     * User : Steven
     * Date : 2024-07-01 14:42
     * @param $param
     */
    public function summaryExport($param)
    {
        $xlsName = "用户留存导出";
        $xlsCell = array(
            array('record_date', '日期'),
            array('promote_realname', '推广员'),
            array('package_name', '游戏包名称'),
            array('register_num', '新增用户数'),
            array('d2', '两日留存'),
            array('d3', '3日留存'),
            array('d4', '4日留存'),
            array('d5', '5日留存'),
            array('d6', '6日留存'),
            array('d7', '7日留存'),
            array('d14', '14日留存'),
            array('d30', '30日留存'),
            array('d60', '60日留存'),
            array('d90', '90日留存'),
            array('d180', '180日留存'),
            array('d360', '360日留存'),

        );

        $param = request()->param();
        $map   = [];
        if (!empty($param['promote_id'])) {
            $map[] = ['promote_id', 'in', $param['promote_id']];
        }
        if (!empty($param['time_range'])) {
            // $param['time_range'][0] = implode('', explode('-', $param['time_range'][0]));
            // $param['time_range'][1] = implode('', explode('-', $param['time_range'][1]));
            $map[] = ['record_date', '>=', $param['time_range'][0]];
            $map[] = ['record_date', '<=', $param['time_range'][1]];
        }
        if (!empty($param['package_id'])) {
            $map[] = ['package_id', 'in', $param['package_id']];
        }
        if (!empty($param['register_source'])) {
            $map[] = ['register_source', '=', $param['register_source']];
        }

        $PromotePackageRemainList = (new PromotePackageRemain)->where($map)
            ->order('record_date', 'desc');
        $list                     = $PromotePackageRemainList
            ->field('id,record_date,promote_id,package_id,register_source,sum(register_num) as register_num,sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d60) as d60,sum(d90) as d90,sum(d180) as d180,sum(d360) as d360')
            ->group('record_date')
            ->select();
        $arr                      = [2, 3, 4, 5, 6, 7, 14, 30, 60, 90, 180, 360];
        foreach ($list as $key => &$val) {
            $val['promote_realname'] = "全部渠道";
            $val['package_name']     = "全部游戏";
            if (!empty($param['promote_id'])) {
                if (count($param['promote_id']) > 1) {
                    $val['promote_realname'] = "多个渠道";
                } else {
                    $val['promote_realname'] = (new UserPromote())->whereIn('id', $param['promote_id'])->value('realname');
                }
            }
            if (!empty($param['package_id'])) {
                if (count($param['package_id']) > 1) {
                    $val['package_name'] = "多个游戏";
                } else {
                    $val['package_name'] = (new Package())->whereIn('id', $param['package_id'])->value('package_name');
                }
            }
            foreach ($arr as $item) {
                $val['d' . $item] = empty($val['register_num']) ? "0%" : round($val['d' . $item] / $val['register_num'] * 100, 2) . "%";
            }
        }
        $xlsData = $list;
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }


    /**
     * Desc : 付费用户留存导出
     * User : Steven
     * Date : 2024-07-01 14:42
     * @param $param
     */
    public function payingUserExport($param)
    {
        $xlsName = "付费用户留存导出";
        $xlsCell = array(
            array('record_date', '日期'),
            array('promote_realname', '推广员'),
            array('package_name', '游戏包名称'),
            array('pay_num', '新增付费用户数'),
            array('d2', '两日留存'),
            array('d3', '3日留存'),
            array('d4', '4日留存'),
            array('d5', '5日留存'),
            array('d6', '6日留存'),
            array('d7', '7日留存'),
            array('d14', '14日留存'),
            array('d30', '30日留存'),
            array('d60', '60日留存'),
            array('d90', '90日留存'),
            array('d180', '180日留存'),
            array('d360', '360日留存'),
        );
        $param   = request()->param();
        $map     = [];
        if (!empty($param['promote_id'])) {
            $map[] = ['promote_id', 'in', $param['promote_id']];
        }
        if (!empty($param['time_range'])) {
            // $param['time_range'][0] = implode('', explode('-', $param['time_range'][0]));
            // $param['time_range'][1] = implode('', explode('-', $param['time_range'][1]));
            $map[] = ['record_date', '>=', $param['time_range'][0]];
            $map[] = ['record_date', '<=', $param['time_range'][1]];
        }
        if (!empty($param['package_id'])) {
            $map[] = ['package_id', 'in', $param['package_id']];
        }
        if (!empty($param['register_source'])) {
            $map[] = ['register_source', '=', $param['register_source']];
        }

        $PromotePackagePayRemainList = (new PromotePackagePayRemain)->where($map)
            ->order('record_date', 'desc');
        $list                        = $PromotePackagePayRemainList
            ->field('id,record_date,promote_id,package_id,register_source,sum(pay_num) as pay_num,sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d60) as d60,sum(d90) as d90,sum(d180) as d180,sum(d360) as d360')
            ->group('record_date')
            ->select();
        $arr                         = [2, 3, 4, 5, 6, 7, 14, 30, 60, 90, 180, 360];
        foreach ($list as $key => &$val) {
            $val['promote_realname'] = "全部渠道";
            $val['package_name']     = "全部游戏";
            if (!empty($param['promote_id'])) {
                if (count($param['promote_id']) > 1) {
                    $val['promote_realname'] = "多个渠道";
                } else {
                    $val['promote_realname'] = (new UserPromote())->whereIn('id', $param['promote_id'])->value('realname');
                }
            }
            if (!empty($param['package_id'])) {
                if (count($param['package_id']) > 1) {
                    $val['package_name'] = "多个游戏";
                } else {
                    $val['package_name'] = (new Package())->whereIn('id', $param['package_id'])->value('package_name');
                }
            }
            foreach ($arr as $item) {
                $val['d' . $item] = empty($val['pay_num']) ? "0%" : round($val['d' . $item] / $val['pay_num'] * 100, 2) . "%";
            }
        }
        // halt($list);
        $xlsData = $list;
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * Desc : 持续付费率导出
     * User : Steven
     * Date : 2024-07-01 14:42
     * @param $param
     */
    public function payingContinueExport($param)
    {
        $xlsName = "持续付费率导出";
        $xlsCell = array(
            array('record_date', '日期'),
            array('promote_realname', '推广员'),
            array('package_name', '游戏包名称'),
            array('d1', '当日新增付费用户数'),
            array('d2', '两日付费率'),
            array('d3', '3日付费率'),
            array('d4', '4日付费率'),
            array('d5', '5日付费率'),
            array('d6', '6日付费率'),
            array('d7', '7日付费率'),
            array('d14', '14日付费率'),
            array('d30', '30日付费率'),
            array('d60', '60日付费率'),

        );
        $param   = request()->param();
        $map     = [];
        if (!empty($param['promote_id'])) {
            $map[] = ['promote_id', 'in', $param['promote_id']];
        }
        if (!empty($param['time_range'])) {
            $param['time_range'][0] = implode('', explode('-', $param['time_range'][0]));
            $param['time_range'][1] = implode('', explode('-', $param['time_range'][1]));
            $map[]                  = ['record_date', '>=', $param['time_range'][0]];
            $map[]                  = ['record_date', '<=', $param['time_range'][1]];
        }
        if (!empty($param['package_id'])) {
            $map[] = ['package_id', 'in', $param['package_id']];
        }
        if (!empty($param['register_source'])) {
            $map[] = ['register_source', '=', $param['register_source']];
        }
        $PromotePackagePayRateList = (new PromotePackagePayRate)->where($map)->order('record_date', 'desc');
        $list                      = $PromotePackagePayRateList
            ->field('id,record_date,promote_id,package_id,register_source,sum(d1) as d1,sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d60) as d60')
            ->group('record_date')
            ->select();
        $arr                       = [2, 3, 4, 5, 6, 7, 14, 30, 60];
        foreach ($list as $key => &$val) {
            $val['promote_realname'] = "全部渠道";
            $val['package_name']     = "全部游戏";
            foreach ($arr as $item) {
                $val['d' . $item] = empty($val['d1']) ? "0%" : round($val['d' . $item] / $val['d1'] * 100, 2) . "%";
            }
            if (!empty($param['promote_id'])) {
                if (count($param['promote_id']) > 1) {
                    $val['promote_realname'] = "多个渠道";
                } else {
                    $val['promote_realname'] = (new UserPromote())->whereIn('id', $param['promote_id'])->value('realname');
                }
            }
            if (!empty($param['package_id'])) {
                if (count($param['package_id']) > 1) {
                    $val['package_name'] = "多个游戏";
                } else {
                    $val['package_name'] = (new Package())->whereIn('id', $param['package_id'])->value('package_name');
                }
            }
        }
        $xlsData = $list;
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * Desc : 用户LTV导出
     * User : Steven
     * Date : 2024-07-01 14:42
     * @param $param
     */
    public function userLtvExport($param)
    {
        $xlsName = "用户LTV导出";
        $xlsCell = array(
            array('record_date', '日期'),
            array('promote_realname', '推广员'),
            array('package_name', '游戏包名称'),
            array('register_num', '新增用户数'),
            array('d_all', '总LTV'),
            array('d1', '1日LTV'),
            array('d2', '两日LTV'),
            array('d3', '3日LTV'),
            array('d4', '4日LTV'),
            array('d5', '5日LTV'),
            array('d6', '6日LTV'),
            array('d7', '7日LTV'),
            array('d14', '14日LTV'),
            array('d30', '30日LTV'),
            array('d60', '60日LTV'),
            array('d90', '90日LTV'),
            array('d180', '180日LTV'),
            array('d360', '360日LTV')
        );
        $param   = request()->param();
        $map     = [];
        if (!empty($param['promote_id'])) {
            $map[] = ['promote_id', 'in', $param['promote_id']];
        }
        if (!empty($param['time_range'])) {
            // $param['time_range'][0] = implode('', explode('-', $param['time_range'][0]));
            // $param['time_range'][1] = implode('', explode('-', $param['time_range'][1]));
            $map[] = ['record_date', '>=', $param['time_range'][0]];
            $map[] = ['record_date', '<=', $param['time_range'][1]];
        }
        if (!empty($param['package_id'])) {
            $map[] = ['package_id', 'in', $param['package_id']];
        }
        if (!empty($param['register_source'])) {
            $map[] = ['register_source', '=', $param['register_source']];
        }

        $PromotePackageLtvList = (new PromotePackageLtv())->where($map)
            ->order('record_date', 'desc');
        $list                  = $PromotePackageLtvList
            ->field('id,record_date,promote_id,package_id,register_source,sum(register_num) as register_num,sum(d_all) as d_all,sum(d1) as d1,sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d45) as d45,sum(d60) as d60,sum(d90) as d90,sum(d120) as d120,sum(d180) as d180,sum(d360) as d360')
            ->group('record_date')
            ->select();
        $arr                   = [1, 2, 3, 4, 5, 6, 7, 14, 30, 45, 60, 90, 120, 180, 360];
        foreach ($list as $key => &$val) {
            $val['promote_realname'] = "全部渠道";
            $val['package_name']     = "全部游戏";
            if (!empty($param['promote_id'])) {
                if (count($param['promote_id']) > 1) {
                    $val['promote_realname'] = "多个渠道";
                } else {
                    $val['promote_realname'] = (new UserPromote())->whereIn('id', $param['promote_id'])->value('realname');
                }
            }
            if (!empty($param['package_id'])) {
                if (count($param['package_id']) > 1) {
                    $val['package_name'] = "多个游戏";
                } else {
                    $val['package_name'] = (new Package())->whereIn('id', $param['package_id'])->value('package_name');
                }
            }
            $val['d_all'] = empty($val['register_num']) ? "0" : round($val['d_all'] / $val['register_num'], 2);

            $day = diffTimeRetrunDay(strtotime($val['record_date']), strtotime(date("Y-m-d")));
            $day = $day + 1;
            foreach ($arr as $item) {
                if ($day > $item) {
                    $val['d' . $item] = empty($val['register_num']) ? "0" : round($val['d' . $item] / $val['register_num'], 2);
                } else {
                    $val['d' . $item] = 0;
                }
            }
        }
        $xlsData = $list;
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * Desc : 付费LTV导出
     * User : Steven
     * Date : 2024-07-01 14:42
     * @param $param
     */
    public function payingLtvExport($param)
    {
        $xlsName = "付费LTV导出";
        $xlsCell = array(
            array('record_date', '日期'),
            array('promote_realname', '推广员'),
            array('package_name', '游戏包名称'),
            array('register_num', '新增用户数'),
            array('d_all', '总LTV'),
            array('d1', '当日LTV'),
            array('d2', '两日LTV'),
            array('d3', '3日LTV'),
            array('d4', '4日LTV'),
            array('d5', '5日LTV'),
            array('d6', '6日LTV'),
            array('d7', '7日LTV'),
            array('d14', '14日LTV'),
            array('d30', '30日LTV'),
            array('d60', '60日LTV'),
            array('d90', '90日LTV'),
            array('d180', '180日LTV'),
            array('d360', '360日LTV'),

        );
        $param   = request()->param();
        $map     = [];
        if (!empty($param['promote_id'])) {
            $map[] = ['promote_id', 'in', $param['promote_id']];
        }
        if (!empty($param['time_range'])) {
            $param['time_range'][0] = implode('', explode('-', $param['time_range'][0]));
            $param['time_range'][1] = implode('', explode('-', $param['time_range'][1]));
            $map[]                  = ['record_date', '>=', $param['time_range'][0]];
            $map[]                  = ['record_date', '<=', $param['time_range'][1]];
        }
        if (!empty($param['package_id'])) {
            $map[] = ['package_id', 'in', $param['package_id']];
        }
        if (!empty($param['register_source'])) {
            $map[] = ['register_source', '=', $param['register_source']];
        }

        $PromotePackagePayLtvList = (new PromotePackagePayLtv())->where($map)
            ->order('record_date', 'desc');
        $list                     = $PromotePackagePayLtvList
            ->field('id,record_date,promote_id,package_id,register_source,sum(register_num) as register_num,sum(d_all) as d_all,sum(d1) as d1,sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d14) as d14,sum(d30) as d30,sum(d45) as d45,sum(d60) as d60,sum(d90) as d90,sum(d120) as d120,sum(d180) as d180,sum(d360) as d360')
            ->group('record_date')
            ->select();
        $arr                      = [1, 2, 3, 4, 5, 6, 7, 14, 30, 45, 60, 90, 120, 180, 360];
        foreach ($list as $key => &$val) {
            $val['promote_realname'] = "全部渠道";
            $val['package_name']     = "全部游戏";
            if (!empty($param['promote_id'])) {
                if (count($param['promote_id']) > 1) {
                    $val['promote_realname'] = "多个渠道";
                } else {
                    $val['promote_realname'] = (new UserPromote())->whereIn('id', $param['promote_id'])->value('realname');
                }
            }
            if (!empty($param['package_id'])) {
                if (count($param['package_id']) > 1) {
                    $val['package_name'] = "多个游戏";
                } else {
                    $val['package_name'] = (new Package())->whereIn('id', $param['package_id'])->value('package_name');
                }
            }
            $val['d_all'] = empty($val['register_num']) ? "0" : round($val['d_all'] / $val['register_num'], 2) ;
            $day = diffTimeRetrunDay(strtotime($val['record_date']), strtotime(date("Y-m-d")));
            $day = $day + 1;
            foreach ($arr as $item) {
                if ($day > $item) {
                    $val['d' . $item] = empty($val['register_num']) ? "0" : round($val['d' . $item] / $val['register_num'], 2);
                } else {
                    $val['d' . $item] = 0;
                }
            }
        }
        $xlsData = $list;
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * Desc : 充值订单导出
     * User : Steven
     * Date : 2024-07-30 09:47
     * @param $where
     */
    public function BoxRechargeOrderExport($where)
    {
        $xlsName = "充值订单导出";
        $xlsCell = array(
            array('id', '序号'),
            array('member_id', '用户ID'),
            array('nickname', '用户昵称'),
            array('game_name', '游戏名称'),
            array('product_id', '计费点'),
            array('product_name', '计费点名称'),
            array('server_name', '区服名称'),
            array('role_name', '角色名称'),
            array('order_num', '订单编号'),
            array('transaction_id', '第三方订单号'),
            array('pay_amount', '支付金额'),
            array('price', '原价'),
            array('discount', '折扣'),
            array('discount_price', '折后价'),
            array('send_status', '订单状态(0、未发货，1、已发货，-1、发货失败)'),
            array('pay_type', '支付方式'),
            array('pay_status', '支付状态'),
            array('pay_time', '支付时间'),
            array('create_time', '订单创建时间'),
        );

        $model = new BoxRechargeOrder();
        $xlsData = $model->alias('a')
            ->leftJoin('cq_member b', 'a.member_id = b.id')
            ->field('a.*,b.nickname')
            ->where($where)->order('id','desc')->select()->toArray();

        $game_ids = (new BoxRechargeGame())->column( 'game_id');
        $game_list = (new BoxGame())->whereIn('id', $game_ids)->column('game_name', 'id');
        array_walk($xlsData, function (&$v) use ($game_list) {
            $v['game_name'] = $game_list[$v['game_id']] ?? '';
        });
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    public function BoxMemberInfoExport($map)
    {
        $xlsName = "盒子用户信息导出";
        $xlsCell = array(
            array('id', '用户ID'),
            array('nickname', '用户昵称'),
            array('username', '用户账号'),
            array('sex', '性别'),
            array('bind_wechat', '绑定微信'),
            array('log_off', '用户状态(0=未注销,1=申请注销,2=已注销)'),
            array('age_status', '实名状态(0=未实名,1=已实名,2=未成年)'),
            array('birthday', '生日'),
            array('score', '用户积分'),
            array('create_time', '注册时间'),
        );
        $memberModel = (new BoxMemberInfo())->alias('m')
            ->leftJoin('cq_member a', 'a.id = m.member_id')
            ->leftJoin('cq_box_member_account c', 'a.id = c.member_id')
            ->leftJoin('cq_ban_log b', 'b.member_id = m.member_id and b.type = 1')
            ->leftJoin(Env('Db_log.database') . '.cq_register_log l', 'l.member_id = m.member_id')
            ->field('a.id as mid,m.id,a.nickname,a.username,a.phone,a.bind_wechat,a.status,a.age_status,a.avatar,m.sex,m.score,m.birthday,
                     c.spend_amount,a.create_time,b.ban_time,b.ban_rule,a.log_off,b.id as bid,l.platform')
            ->where($map);

        if (!empty($order) && !empty($sort)) {
            $orderBy = 'a.' . $sort . ' ' . $order;
        } else {
            $orderBy = 'a.id desc';
        }
        $memberModel = $memberModel->order($orderBy);
        $xlsData        = $memberModel->select()->toArray();
        $memberIds   = array_column($xlsData, 'mid');
        $memberLog   = RegisterLog::whereIn('member_id', $memberIds)->field('member_id,platform')->select()->toArray();
        $memberLog   = array_column($memberLog, 'platform', 'member_id');
        foreach ($xlsData as &$value) {
            $value['platform'] = $memberLog[$value['mid']] ?? '';
        }
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }

    /**
     * Notes: VIP订单导出
     * User: liubaoqiang
     * DateTime: 2024/11/5 11:04
     * @param $map
     */
    public function BoxVipOrderListExport($map)
    {
        $xlsName = "盒子VIP订单列表导出";
        $xlsCell = array(
            array('id', '用户ID'),
            array('nickname', '用户昵称'),
            array('username', '用户账号'),
            array('order_num', '订单号'),
            array('pay_amount', '支付金额'),
            array('price', '原价'),
            array('pay_status', '支付状态(0=未支付,1=已支付)'),
            array('platform', '下单平台(平台 1=ios,2=安卓,3=pc)'),
            array('transaction_id', '第三方订单号'),
            array('pay_type', '支付方式'),
            array('pay_time', '支付时间'),
            array('create_time', '订单创建时间'),
        );
        $xlsData = (new BoxVipOrder())->alias('a')
            ->leftJoin('cq_member m', 'a.member_id=m.id')
            ->field('a.*,m.nickname,m.username')
            ->where($map)
            ->order('a.id', 'desc')
            ->select()
            ->toArray();
        $this->exportExcel($xlsName, $xlsCell, $xlsData);
    }


}
